Problem retrieving DateTime object using Sql
Given the date, 2009/04/30, in one of the lines, I want to get all the dates <= 2009/04/30 and> = 2009/04/30. The sql statements are as follows:
select dateColumn from someTable where dateColumn <= '2009/4/30'
select dateColumn from someTable where dateColumn >= '2009/4/30'
The above 2 commands are executed, but the first statement returns all dates below 2009/04/30, it seems to exclude the date even if it appears in the DB. Any idea why this might be happening? How do I compare the date of a DateTime object in sql?
source to share
SQL Server
stores dates along with times.
select dateColumn from someTable where dateColumn <= '2009/4/30'
returns all dates less than or equal to 2009/4/30 00:00:00
.
If your date is at least 1/300
seconds longer, e. g. 2009/4/30 00:00:00.003
, it will not be refunded.
You need to choose 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')
source to share
Does the column have time data? As written, the first query will return records where dateColumn is less than or equal to 2009/4/30 at 12:00 (midnight). You can use CAST or CONVERT to compare only temporary chunks, but in this case, the simplest answer is to change the first query to:
select dateColumn from someTable where dateColumn < '2009/5/1'
source to share
Don't compare dates with strings.
http://rtipton.wordpress.com/2009/01/18/converting-dates-in-sql-server/
In terms of getting the date, dates are displayed inside floating point numbers, where 1 is one day.
CONVERT(DATETIME, CONVERT(INT, GETDATE()))
Date and time conversion will be performed.
source to share
When you specify a date as a string and do not specify a time, it uses midnight (12:00 AM) of that date. If you want to include all times on a given date (instead of midnight), use a comparison less than the next date, which is easier and more accurate than using 11:59:59 PM.
select dateColumn from someTable where dateColumn < '2009/5/1'
Alternatively, you can specify the date to exclude the time, but you need to be careful to order the comparison like yyyymmdd
to get the order correct. Converting a string to a date will work as well, but you still need to make sure you are comparing midnight of the next day and not midnight on the corresponding date.
select dateColumn from someTable where CONVERT(varchar,dateColumn,112) <= '2090430'
source to share
The MySQL documentation page has a very useful list of helper functions that you could use. What you are doing in your query is comparing a string, not a date. It might work if you write the month as two digits 04 instead of 4. In any case, you better convert to UNIX_TIMESTAMP and compare accordingly.
source to share
Anything that creates your data includes times other than 12:00 AM.
I found this at: Jeff's SQL Server Blog and use it to get rid of the time when all I want is a date:
dateadd(dd,0, datediff(dd,0,@DateTime))
So your request could be:
select dateColumn from someTable where dateadd(dd,0, datediff(dd,0, [dateColumn]))<= '2009/4/30'
I made it a function because it is used a lot.
source to share