Querying in between to dates

Question

1.00/5 (1 vote)

See more:

Hi friends,
I have a doubt in SQL regarding querying this table

SQL

CREATE TABLE [dbo].[tbl_Trans_LeaveRequestApproval]
(
    [FromDate] [datetime] NOT NULL,
[ToDate] [datetime] NOT NULL,
    -- other columns to follow here
    -- ...
)

Suppose I have inserted a record with FromDate as '21-05-2012' and ToDate as '28-05-2012' and now I want the dates inbetween the two dates.
How do i make a query for this?

Thanks in advance!

Posted 21-May-12 1:44am

echosound

Updated 21-May-12 1:53am

Manfred Rudolf Bihy

v3

Add a Solution

Comments

Manfred Rudolf Bihy 21-May-12 7:56am

You are being unclear: Do you wand the number of days between these two dates. You said you wanted "the dates between the two dates", what exactly do you mean by that? You probably mean records with dates between the two mentioned.

echosound 21-May-12 7:57am

dates from the fromdate and todate

6 solutions

Solution 1

Try
SELECT * FROM myTable WHERE myDateField BETWEEN '21-05-2012' AND '28-05-2012'

Posted 21-May-12 1:48am

Abhinav S

Comments

Mohamed Mitwalli 21-May-12 8:07am

5+

Abhinav S 21-May-12 9:14am

Thanks.

Solution 2

refer http://database.ittoolbox.com/groups/technical-functional/sql-l/selecting-between-two-dates-within-a-datetime-field-sql-server-610522[^]
you have to use BETWEEN operator ..

Posted 21-May-12 1:49am

leena206

Comments

Manfred Rudolf Bihy 21-May-12 8:05am

Right on! 5+

Mohamed Mitwalli 21-May-12 8:07am

5+

Solution 3

See this link you may get some idea
http://msdn.microsoft.com/en-us/library/ms189794.aspx[^]

Posted 21-May-12 1:55am

vangapally Naveen Kumar

Comments

Manfred Rudolf Bihy 21-May-12 8:05am

Until OP explains exactly what he wants, that too is an option! 5+

Solution 5

Another twist on the previous solutions (since you didn't exaclty specify what you wanted):

SQL

SELECT *
FROM tbl_Trans_LeaveRequestApproval ra
WHERE
    ra.FromDate >= '2012-05-21' AND
    ra.ToDate   <= '2012-05-28

or maybe you wanted the difference of days:

SQL

SELECT DATEDIFF(day, '2012-05-28', '2012-05,21') AS NumberOfDays

Regards,

Manfred

Posted 21-May-12 1:59am

Manfred Rudolf Bihy

Updated 21-May-12 2:01am

v2

Comments

Mohamed Mitwalli 21-May-12 8:06am

5+

Solution 6

Dear Friend,

Try this, I hope this will fulfill your requirement.
But its taken from Net.

SQL

SET DATEFORMAT MDY
DROP TABLE #myBalances
CREATE TABLE #myBalances (
      [TranDate] DATETIME,
      [TransactionAmount] money,
      [Balance] money)
      
INSERT INTO #myBalances VALUES ('5/1/2012',100,100)
INSERT INTO #myBalances VALUES ('5/3/2012',-60,40)
INSERT INTO #myBalances VALUES ('5/4/2012',10,50)



DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = '5/21/2012'
SET @EndDate = '5/28/2012'

;with TallyCalendar as (
                       --5 years prior to today plus 5 years after
                       SELECT  dateadd( dd, -3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N
                       FROM (
                             SELECT TOP 7300 -- ~10 years of days
                               row_number() OVER (ORDER BY sc1.id) AS RW 
                             FROM Master.dbo.SysColumns sc1
                             CROSS JOIN Master.dbo.SysColumns sc2
                             ) X
                      )

SELECT DATEADD(DD, 0, Tally.n) AS TranDate
FROM TallyCalendar Tally 
LEFT JOIN #myBalances b ON b.TranDate = DATEADD(DD, 0, Tally.n)  
LEFT JOIN #myBalances x ON x.TranDate = (SELECT MAX(TranDate) AS TranDate FROM #myBalances WHERE TranDate <= DATEADD(DD, 0, Tally.n))
WHERE n BETWEEN DATEDIFF(DD, 0, @StartDate) AND DATEDIFF(DD, 0, @EndDate)

Regards,
AP

Posted 21-May-12 3:37am

Arunprasath Natarajan

Updated 21-May-12 7:44am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Accepted Answer · 2012-05-21T01:56:00

Solution 4

Try this:

SQL

SELECT * FROM tbl_Trans_LeaveRequestApproval WHERE FromDate >= '21-05-2012' AND ToDate <= '28-05-2012'

Posted 21-May-12 1:56am

Prasad_Kulkarni

Comments

Manfred Rudolf Bihy 21-May-12 8:02am

You beat me to it! 5+

echosound 21-May-12 8:07am

no i want the date between those two dates

Prasad_Kulkarni 21-May-12 8:09am

So therefore I answered it. Is there any problem you're facing with that query?

echosound 21-May-12 8:10am

ya really want the dates based on the employee id!!!

Prasad_Kulkarni 21-May-12 8:16am

SELECT * FROM tbl_Trans_LeaveRequestApproval WHERE FromDate >= '21-05-2012' AND ToDate <= '28-05-2012' WHERE id = '1'

like this?

echosound 21-May-12 8:21am

no not like ok thank u i try it from my own !!!
BYE

Prasad_Kulkarni 21-May-12 8:23am

See echosound, if you post your question more clearly you will get answer that much quicker. So please elaborate each time your question. Because we're answering you by what you post here.

Hope you getting me.

echosound 21-May-12 8:31am

ya ok sure !!!
select DATEDIFF(DD,fromdate,todate) from tbl_Trans_LeaveRequestApproval from this query am getting the noofdays but i want the date(dd-mm-yyyy)

Prasad_Kulkarni 21-May-12 8:48am

Try this:

SELECT DATEDIFF(DD,fromdate,todate), CONVERT(VARCHAR(10), YourDateColumnField, 105, AS [DD-MM-YYYY]) FROM tbl_Trans_LeaveRequestApproval

echosound 21-May-12 9:34am

SELECT DATEDIFF(DD,fromdate,todate), CONVERT(VARCHAR(10), YourDateColumnField, 105, AS [DD-MM-YYYY]) FROM tbl_Trans_LeaveRequestApproval
i tried this but it showing error !!!

Prasad_Kulkarni 22-May-12 0:45am

What's the error?

Prasad_Kulkarni 21-May-12 8:08am

:) hmmm, Thank you.

Mohamed Mitwalli 21-May-12 8:06am

5+

Prasad_Kulkarni 21-May-12 8:08am

Thank you Mohamed!