GETDATE() - Yesterday or before

[NavMen]

Hi,

I'm using the following statement for reporting information on daily basis:

convert(char(10),GETDATE(),101) = convert(char(10),sscreate1,101)

But, how can I report information sinds yesterday or 12 days ago with the GETDATE?

Thanks,

J

 

[amorous]

dateadd('dd', -1, GETDATE()) --> for yesterday

dateadd('dd', -12, GETDATE()) ---> for 12 days ago


-VJ

 

[SQLBill]

GETDATE()-1 is yesterday
GETDATE()-12 is 12 days ago.

Test it in Query Analyzer, just run:

SELECT GETDATE() AS 'Today',
GETDATE()-1 AS 'Yesterday',
GETDATE()-12 AS '12 Days Ago'

-SQLBill

 

[NavMen]

Thanks for the answers, with GETDATE()-2 I got the info from 2 days ago, but how can I report information from 2 days ago to today? (a two days periode)

Thanks already,
J

 

[cLFlaVA]

Use the BETWEEN comparison

SELECT *
FROM   table_name
WHERE  column_name BETWEEN GETDATE() AND (GETDATE()-2)

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[ESquared]

Make sure you're getting the results you want. If it is 4:30pm today, then GetDate()-1 will be 4:30 pm yesterday. So it's better to think of it as GetDate() - (24 hours).

I recommend against doing

convert(char(10),GETDATE(),101) = convert(char(10),sscreate1,101)

because then the query engine must do a conversion on every row in your table just to pull the records you want. Instead, I would do this:

WHERE
   sscreate1 >= convert(int,convert(floor,GetDate()))
   AND sscreate1 < convert(int,convert(floor,GetDate())) + 1/code]

There are all sorts of methods you can use to convert the date+time that is GetDate() into just the part that is the date. You can use your method, too:

[code]WHERE
   sscreate1 >= Convert(char(10),GetDate(),101)
   AND sscreate1 < Convert(datetime,Convert(char(10),GetDate(),101)) + 1

Note that in both of these I am doing implicit conversion back to datetime. The query engine sees sscreate1 first and knows there will be a datetime comparison, and it is able to convert strings and integers back to dates with no problem. However, in the last comparison, I needed to add 1 and that doesn't work well with a string, so I had to convert it to datetime explicitly.

Also please note how the first comparison is >= and the second one is <. This ensures that the 12am this morning is included, but 12am midnight (tomorrow's morning) is not.

Now, back to your original question: you can select any day you want just by adding or subtracting, as others have told you. If you want whole days without the time part, then do the additional stuff I showed you here.

-- 12 days ago:

WHERE
   sscreate1 >= Convert(datetime,Convert(char(10),GetDate(),101)) - 12
   AND sscreate1 < Convert(datetime,Convert(char(10),GetDate(),101)) - 11

The reason I suggest using this format is because by having sscreate1 on one side of the comparison operator by itself, the query engine can use an index on it. Also, no conversion will be done on every row in the database and it should be very fast: the datetime expressions you are comparing to will be evaluated only once, so it hardly matters how bad they are.

-------------------------------------
A sacrifice is harder when no one knows you've made it.