Find Records Between a Given Date??

Posted by Santosh4u under Sql Server on 10/10/2009 | Views : 5834 | Status : [Member] | Replies : 8
hi
can any plz help me how to find records between a given Date Time Range.

Ex:Let there are two columns in Table Like(FromDate(DateTime),ToDate(DateTime))
Table Records Are Like
ID FromDate ToDate
1 12/09/2008 12:10:00 AM 07/04/2009 12:10:00 AM
2 24/12/2008 12:10:00 AM 16/01/2009 10:40:12 PM

etc

now if i am passing fromDate = 12/08/2006 and ToDate = 12/12/2009 then it should come come all the records between this range(checking Time Also)
Plz Help Me,Urgent
Santosh,Bangalore




Responses

Posted by: Navi1478 on: 6/10/2013 [Member] Starter | Points: 25

Up
0
Down
In My database date format is dd/mm/yyyy and not able to apply betwee query on that format

1)select * from tbstd_rec where joiningdate between '1/6/2013' and '10/6/2013'
Get all record where 1/6 -10/6 exists either in 2009,2010 record ..Very vast database and not able to edit datetime format ..please help me


Regards
Navi

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Virendradugar on: 10/11/2009 [Member] [MVP] Silver

Up
0
Down
Hi Santhosh,

You can easily do that using > and < operator.

Select * from Table11
Where FromDate >= @FromDate and ToDate<= @Todate


Hope this helps...

Thanks,
Virendra

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tridev_2005 on: 10/12/2009 [Member] Starter

Up
0
Down
Try this code

select * from urTable Where urDateColumn Between '12/09/2008 12:10:00 AM' and '07/04/2009 12:10:00 AM'

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: SqlDeveloper on: 10/13/2009 [Member] Starter

Up
0
Down
Hello,
I think we can use the below sql query
This select uses the BETWEEN operator.
If you have an index on one of the columns including the other column, you will also benefit from the index seek speed.

select * from Dates 

where
fromdate between @fromDate and @ToDate
and todate between @fromDate and @ToDate



Eralper
http://www.kodyaz.com


Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Santosh4u on: 10/19/2009 [Member] Bronze

Up
0
Down
this is not working because i am passing like '12/09/2008'and '12/02/2009'
Regards
Santosh

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dbdon on: 11/12/2009 [Member] Starter

Up
0
Down
Basically, I dont see any reason for why your code is not working, but still if it is not, you can convert your datetime into varchar and do the comparison.

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Chikul on: 11/28/2009 [Member] Bronze

Up
0
Down
Hi Santosh,

U can Change the date Format(using split) to "mm/dd/yyyy" format while passing & then Use it to get the details.

Dim strDate as String=""
txtDate.Text="22/08/2009"
Dim strTempDate() As String = txtDate.Text.Split("/")
strDate = strTempDate(1) & "/" & strTempDate(0) & "/" & strTempDate(2)

Here U'll get the Date as "08/22/2009". so, now u pass it to the Database to retrive the deatails.

Regards,
Chikul

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Chikul on: 11/28/2009 [Member] Bronze

Up
0
Down
Hi Santosh,

Now pass the dates in "mm/dd/yyyy" format & then Use it to get the details based on the fromDate & ToDate range.

DECLARE @FromDate AS DATETIME,@ToDate AS DATETIME
SELECT @FromDate='11/28/2009',@ToDate='11/28/2009'
SELECT @ToDate=@ToDate+0.999999999
Select * from <TABLE1>
where @FromDate>=CONVERT(VARCHAR,@FromDate,121) and @ToDate<=CONVERT(VARCHAR,@ToDate,121)

Regards,
Chikul

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response