Between data problems...

Question

Between data problems...

Dave Rooke

SSC Eights!

Points: 906
More actions
January 30, 2007 at 4:24 am

#174584

I am having problems trying to get a date selection to work correctly....
I have a smalldatetime column for which i am trying to search between two dates, but i get the following problem.
SQL part:
AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME >= '04/01/2007')
AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME <= '05/01/2007')
This only gives me records for 04/01/2007 and not inclusive of the 05/01/2007?
I have also tried BETWEEN, which gives me the exact same results?
If i change 05/01/2007 to 06/01/2007, then i get 04 and 05 records, but not inclusive of 06.
Thanks in advance for help on this matter....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

hi

does the REQUEST_DATE_TIME column contain date as well as time. if so you will have to format this column in your query.

"Keep Trying"

Dave Rooke SSC Eights! Points: 906 More actions · Answer 2

Thanks for that - forgot about that problem...

I did this and got the results correctly:

AND CAST(FLOOR(CAST(dbo.ISSUE_MAIN.REQUEST_DATE_TIME AS FLOAT))AS DATETIME) >= 'From_Date'

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

try using the date in this format YYYYMMDD there is already a thread going in this forum with the same issue and lot of users are answering the same.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Ian Yates SSCoach Points: 19738 More actions · Answer 4

SQL Server doesn't have DATE columns, it only has DATETIME columns. The resolution of times in SQL Server is 3 milliseconds, thus you could do something like

AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME >= '04/01/2007')

AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME = '04/01/2007')

AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME < '06/01/2007')

This is equivalent to saying that you want all numbers starting with 4 or 5 (which would include 4.1, 4.9, 5.0 and 5.1 and 5.9) rather than your original query which is all numbers between 4 and 5 (which would only include 4.1, 4.9 and 5.0). Dates are effectively a floating point value in SQL Server and in most programming languages.

Also, as suggested, you should try to format your dates in an international format such as YYYYMMDD HH:NN:SS.ss . You can also investigate the CONVERT statement which allows you to provide a parameter indicating which datetime string format is being used (Australia/UK vs US, etc).

If that's not clear, please let me know and I'll try to provide more examples. Cheers

Ian Yates SSCoach Points: 19738 More actions · Answer 5

By the way, when I say SQL has DATETIME columns, not DATE, SQL Server interprets a given date that has no time as midnight starting on that date. So the <= '5/01/2007' is interpreted as <= '5/01/2007 00:00' and would exclude times such as '5/01/2007 10:43' because it is technically after midnight on that date. Hence the '5/01/2007 23:59.997' example, or the better 2nd example which uses < rather than <=.