Retrieve only the date from current_timestamp

[suzreid]

I have a query where I want to retrieve the records where a date falls between yesterday and 7 days ago. Because of the timestamp on the current_timestamp I am missing some records. The condition I am using is

where eventdate between current_timestamp-7 and current_timestamp-1

Event date has a timestamp of 00:00:00

Effectively if I run the query at 9am I miss all the records from midnight to 9am.

Is there any way I can just pull back the date?

In Oracle there is a trunc function that allows you to restrict the date to only the date part and by default it takes the time to be midnight regardles of the timestamp.

Susan

 

[gradley]

-- Current Date (No Time)
select dateadd(dd, datediff(dd,0,Getdate()),0)

 

[travisbrown]

OR Convert(CHAR(11),GETDATE(),105) will return just the date without the trailing zeros but I think the currently suggested way is to use DATEADD because it doesn't change datatypes.

dateadd(dd, datediff(dd,0,Getdate()),0): 2004-11-19 00:00:00.000

Convert(CHAR(11),GETDATE(),111): 2004/11/19

 

[SQLBill]

Why are you using CURRENT_TIMESTAMP? It takes less typing to use GETDATE(). Both return the same thing, current date and time.

-SQLBill

 

[ESquared]

A simple way to write the query:

WHERE
   DateDiff(d,eventdate,GetDate()) between 1 and 7

But the problem with this is that a calculation must be done on each eventdate in the database. So use Gradley's solution. But if you do that, an event at midnight this morning will improperly be included because the between operator is inclusive. So you can use

between [dateadd] - 7 ... and [dateadd...] -1.0000000386

-- yields 23:59:59.997 of the day before yesterday, which is the greatest time value allowed in a datetime field.

-- or --

WHERE
   eventdate >= [dateadd...] - 7
   AND eventdate < [dateadd...] - 1

Note how the second condition is less-than, and not less-than-or-equal-to.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)