Issue with retrieving DateTime object using Sql

Question

Given the date, 2009/04/30, in one of the rows I want to retrieve all dates <= 2009/04/30 and >= 2009/04/30. The sql statements are like this:

select dateColumn from someTable where dateColumn <= '2009/4/30'

select dateColumn from someTable where dateColumn >= '2009/4/30'

The above 2 statements run but the first statement returns all dates below 2009/04/30, it seems to be excluding the date even though it appears in the DB. Any idea why this may be happening? How would I compare the date portion of the DateTime object in sql?

Answer 1

SQL Server stores dates along with time.

select dateColumn from someTable where dateColumn <= '2009/4/30'

returns all dates less or equal to 2009/4/30 00:00:00.

If your date is at least 1/300 seconds greater, e. g. 2009/4/30 00:00:00.003, it will not be returned.

You need to select like this:

select dateColumn from someTable where dateColumn < '2009/5/01'

or like this:

select dateColumn from someTable where dateColumn < DATEADD(day, 1, '2009/4/30')

Answer 2

Don't compare dates with strings.

http://rtipton.wordpress.com/2009/01/18/converting-dates-in-sql-server/

As for getting the date portion, dates are internally represented as floating point numbers where 1 is one day.

CONVERT(DATETIME, CONVERT(INT, GETDATE()))

Will do the datetime-to-date conversion you want.

Answer 3

Does the column contain time data? As written, the first query will return records where dateColumn is less than or equal to 2009/4/30 at 12:00 AM (midnight). You can use CAST or CONVERT to compare time portions only, but in this case the simplest answer is to change the first query to:

select dateColumn from someTable where dateColumn < '2009/5/1'

Answer 4

When you specify the date as as string and don't specify a time, it uses midnight (12:00 AM) of that date. If you want to include all of the times on a given date (instead of just midnight), use a comparison of less than the succeeding date -- which is easier and more accurate than using 11:59:59PM.

select dateColumn from someTable where dateColumn < '2009/5/1'

Alternatively you could cast the date to exclude the time, but you need to be careful to order your comparison as yyyymmdd to get the ordering correct. Converting the string to a date would work as well but you still need to make sure that you're comparing midnight of the next day instead of midnight on the date in question.

select dateColumn from someTable where CONVERT(varchar,dateColumn,112) <= '2090430'

Answer 5

The first query is implicitly converting your date string to a datetime. This conversion yields:

2009-04-30 00:00:00.000

Is it possible that the excluded date is on that day yet at a later time that would be excluded by the value above?

Answer 6

In MySQL documentation page there is a very helpful list of helper functions that you could use. What you are doing in your query is comparing string not date. It might work if you write the month as two digits 04 instead of 4. Anyway, you are better off converting to UNIX_TIMESTAMP and comparing according to it.

Answer 7

Whatever is creating your data, is including times other than 12:00 AM.

I found this on: Jeff's SQL Server Blog and use it to get rid of time when all I want is the date:

dateadd(dd,0, datediff(dd,0,@DateTime))

So your query could be:

select dateColumn from someTable where dateadd(dd,0, datediff(dd,0, [dateColumn]))<= '2009/4/30'

I made this a function since it gets used a lot.

Answer 8

select dateColumn from someTable where dateColumn between dateadd(dd,-1,'2009/4/30') and dateadd(dd,1,'2009/4/30')

this query will select columns with dateColumn value between the two dates you've specified including them also.Hope it solves your problem.