SQL query in SQL SERVER 2005 - Comparing Dates

Question

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

stored as varchar? or is this format what you see in client tools? — gbn, Oct 05 '09 at 19:10

anishMarokey · Accepted Answer · 2009-10-05T19:18:40.323

5

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))

edited Oct 05 '09 at 19:18

answered Oct 05 '09 at 19:11

anishMarokey

11,279
2
34
47

score 5 · Answer 2 · edited May 23 '17 at 12:19

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...

score 1 · Answer 3 · answered Oct 05 '09 at 19:15

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

score 0 · Answer 4 · answered Oct 05 '09 at 19:15

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's date (without time)

SET @TomorrowsDate = DATEADD (Day, 1, CONVERT (VARCHAR, GETDATE(), 101))

To use it in a query with a column without declaring a variable

SELECT Column1, Column2
FROM YourTableName
WHERE DateColumn BETWEEN DATEADD (Day, 0, CONVERT (VARCHAR, GETDATE(), 101)) 
    AND DATEADD (Day, 1, CONVERT (VARCHAR, GETDATE(), 101))

SQL query in SQL SERVER 2005 - Comparing Dates

4 Answers4