CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
challengeAccepted
challengeAccepted

Reputation: 7610

composing a SQL query with a date offset

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!

Upvotes: 2

Views: 5176

Answers (5)

Joel Coehoorn
Joel Coehoorn

Reputation: 416039

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

Upvotes: 6

Greg
Greg

Reputation: 33650

select *
  from MyTable
 where DATEADD(dd, 0, DATEDIFF(dd, 0, ChangingDate)) = SELECT DATEADD(dd, 1, DATEDIFF(dd, 0, GETDATE()))

Upvotes: 2

Tejs
Tejs

Reputation: 41256

Sounds like you want the DATEPART function to find where the column date has the same year, month, day regardless of time of day:

SELECT * FROM Table 
WHERE 
    DATEPART(Month, Date) = DATEPART(Month, @SomeDate) 
AND DATEPART(Day, Date) = DATEPART(Day, @SomeDate) 
AND DATEPART(Year, Date) = DATEPART(Year, @SomeDate)

Otherwise, you want to use DateAdd like the other posters.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135111

This will return tomorrow's data

WHERE ChangingDate > = dateadd(dd, datediff(dd, 0, getdate())+1, 0)
and ChangingDate < dateadd(dd, datediff(dd, 0, getdate())+2, 0)

This will return today's data

WHERE ChangingDate > = dateadd(dd, datediff(dd, 0, getdate())+0, 0)
and ChangingDate < dateadd(dd, datediff(dd, 0, getdate())+1, 0)

See also How Does Between Work With Dates In SQL Server?

Upvotes: 5

Lav
Lav

Reputation: 1896

you may want to try something like

select * from table where columndate=GetDate() + 1

Upvotes: 0

Related Questions