SQL query in SQL SERVER 2005 - Comparing Dates
Solution 1
declare tomorrow's date : DATEADD(dd,1,getdate())
compare dates :
WHERE column >= CONVERT(datetime, CONVERT(varchar, DATEADD(day, 1, GETDATE()), 102))
AND column < CONVERT(datetime, CONVERT(varchar, DATEADD(day, 2, GETDATE()), 102))
Solution 2
Assumes datetime datatype for columns
WHERE
MyCol >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
AND
MyCol < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 2)
This (yyyy-mm-dd) removes the time component to test of MyCol is tomorrow
2009-10-06 00:00:00 <= MyCol < 2009-10-07 00:00:00
You don't strip time from MyCol: this will affect performance and disallow index usage etc
Efficiency of remove time from datetime question, which is why I used this format and avoid varchar conversions...
Edit:
Avoiding implicit conversions and string matching
10/06/2009 <= MyCol < 10/07/2009
WHERE
MyCol >= CONVERT(char(10), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1), 101)
AND
MyCol < CONVERT(char(10), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 2), 101)
Of course, it'll fail at the end of December...
Solution 3
I would think your dates are most likely to be in SQL Server's datetime datatype, and that the format you give is just the default string representation.
Typically, I use something like:
SELECT *
FROM tbl
WHERE datecol = CONVERT(datetime, CONVERT(varchar, DATEADD(day, 1, GETDATE()), 101))
However, if your datetimes include a time piece, you need to use something like this:
SELECT *
FROM tbl
WHERE datecol >= CONVERT(datetime, CONVERT(varchar, DATEADD(day, 1, GETDATE()), 101))
AND datecol < CONVERT(datetime, CONVERT(varchar, DATEADD(day, 2, GETDATE()), 101))
There are other date arithmetic tricks you can use. There are plenty here on SO if you look for SQL dates
Comments
-
hyeomans almost 2 years
I'm having a hard time doing this query. I want to compare dates in my query, dates from my DB are in this format:
(MM/DD/YYYY HH:MM:SS AM)
I want to compare this date with tomorrow's day, today plus one.
My questions are:How do I declare tomorrow's date in sql server?
How would you compare these two dates?Thank you!! =D
EDIT : DATES in DB are VarChar =S