composing a SQL query with a date offset

Question

I am trying to do this:

select * from table 
where ChangingDate(this is a column which has date and time) = today's date + 1

I am a learner of SQL, and I am bad at date formats. I appreciate if someone can help.

Thank you!

Answer 1

There's a trick with datetimes in databases - you almost never want an = comparison, because as you saw they also include a time component. Instead, you want to know if it falls inside a range that includes the entire day. Sql Server 2008 has a new date type that helps with this, but until you upgrade, do it like this:

WHERE (ChangingDate >= dateadd(dd,1, datediff(dd,0, getDate())) 
       AND ChangingDate < dateadd(dd,2, datediff(dd,0, getDate())))

You can do an equals comparison if you are certain that all the records have a 0-value (or other known value) for the time component in that column. What you don't want to do is truncate the column, because that means doing extra work per-record (slow) and will break your index (very slow).