SQL Server Performance Forum – Threads Archive

BETWEEN Operator

I have a table (table_A) that has a datetime column. When I run the following statement I do not return any records that have a createdate of ‘20040901’ or ‘20040930’. select createdate from table_A where createdate between ‘20040901’ and ‘20040930’ I read in BOL where "The BETWEEN keyword specifies an inclusive range to search", which to my understanding should include (in the above select statement) records having a createdate of ‘20040901’ and ‘20040930’, yet it does not return either. Please help.

Well ‘20040930’ is equivalent to ‘20040930 0:00:00’ so last date is not going to be included, you should use < ‘20041001 0:00:00’

dont you need to format the dates like this: select createdate from table_A where createdate between ‘2004-09-01’ AND ‘2004-09-30’ ??

I feel u can try diiferent date formats like
‘2004-apr-01’ or ‘2004-04-01’ plz let me know: have u assigned column to be of datatype smalldatetime or varchar.

Like mmarovic has mentioned, if you only supply the date, SQL Server will default the time to midnight. Personally I think that’s a good reason to avoid BETWEEN and rather be explicite like
WHERE datetime_column >= @startdate AND datetime_column < @enddate —
Frank
SQL Server MVP
http://www.insidesql.de

Also try to see if the time part of the date needs to be stored at all, and then just make sure that the application sets the date with no time.

…and if so, run an UPDATE against your table to set the time for the existing rows to midnight. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank SQL Server MVP http://www.insidesql.de</a>

]]>