SQL Query in SQL SERVER 2005 - Comparing Dates
I find it difficult to fulfill this request. 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, today plus one.
My questions:
How do I declare the date tomorrow in sql server?
How would you compare these two dates?
Thanks! = D
EDIT: DATES in DB: VarChar = S
source to share
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 for testing MyCol tomorrow
2009-10-06 00:00:00 <= MyCol <2009-10-07 00:00:00
You are not removing time from MyCol: this will affect performance and prevent the use of the index, etc.
Efficiency of removing time from datetime question , so I used this format and avoid varchar conversions ...
Edit:
Preventing implicit conversions and string matching
06/10/2009 <= MyCol <07/10/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 will fail at the end of December ...
source to share
I would think that your dates are most likely to be in the SQL Server 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 dates include a time slice, 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 arithmetic tricks you can use as well. There are many here on SO if you are looking for SQL dates
source to share
SQL Server allows you to declare variables
DECLARE @TomorrowsDate DateTime
And you can set it to the current date and time
SET @TomorrowsDate = DATEADD (Day, 0, GETDATE())
For tomorrow (no time)
SET @TomorrowsDate = DATEADD (Day, 1, CONVERT (VARCHAR, GETDATE(), 101))
To use it in a query with a column without a variable declaration
SELECT Column1, Column2
FROM YourTableName
WHERE DateColumn BETWEEN DATEADD (Day, 0, CONVERT (VARCHAR, GETDATE(), 101))
AND DATEADD (Day, 1, CONVERT (VARCHAR, GETDATE(), 101))
source to share