Between Dates Headache - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Between Dates Headache

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-11-17 : 05:21:09

Hi, I'm sure this is simple and probably because I'm using the wrong syntax's but I've got a table with dates in it all I want to do is select all the records where the date is between today and say last month. The problem is the field also stores the time so when searching misses out the records after the start of the day, is there any way around this without passing in the time too?

Cheers.

Tim

--------
DECLARE @datefrom as datetime, @dateto as datetime

SET DATEFORMAT dmy

SET @datefrom = CAST('17/10/2003' as datetime)
SET @dateto = CAST('17/11/2004' as datetime)

SELECT L_Date
FROM IP_Filter_Log
WHERE ([L_Date] BETWEEN @datefrom AND @dateto)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 05:38:15

try:

SELECT L_Date
FROM IP_Filter_Log
WHERE (DATEADD(Day, DATEDIFF(Day, 0, [L_Date]), 0) BETWEEN @datefrom AND @dateto)

or

SELECT L_Date
FROM IP_Filter_Log
WHERE ([L_Date] BETWEEN dateadd(d, -1, @datefrom) AND dateadd(d, 1, @dateto))

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-17 : 05:56:42

quote:
Originally posted by timgaunt

DECLARE @datefrom as datetime, @dateto as datetime

SET DATEFORMAT dmy

SET @datefrom = CAST('17/10/2003' as datetime)
SET @dateto = CAST('17/11/2004' as datetime)

SELECT L_Date
FROM IP_Filter_Log
WHERE ([L_Date] BETWEEN @datefrom AND @dateto)

not sure if i'm oversimplifying this:

where convert(datetime,l_date,103) between @datefrom and @dateTo

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 06:07:24

i acctually stopped using convert with dates because of indexes....
one of many things you learn here

jen: just 100 more to go.

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-17 : 06:11:30

quote:
Originally posted by spirit1

i acctually stopped using convert with dates because of indexes....
one of many things you learn here

jen: just 100 more to go.

Go with the flow & have fun! Else fight the flow

yah, then i can retire and bask under the carribean sun. lol

--------------------
keeping it simple...

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-11-17 : 06:32:17

Works a treat thanks.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources