add 18 hours to getdate() result

Question

I'm trying to get yesterdays date on 18:00:00 time.

I've tried this:

DECLARE @YesturdayDate datetime = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()-1));

select dateadd(h,18,@YesturdayDate)

but i'm getting an error when run the select statement:

"Must declare the scalar variable "@YesturdayDate". "

why is that?

thanks.

What version of sql-server are you using? I assume 2005. – Tim Schmelter Jan 30 '13 at 09:53 — Tim Schmelter, Jan 30 '13 at 09:53

score 3 · Answer 1 · answered Jan 30 '13 at 09:54

3

What version of sql-server are you using? I assume 2005, then you cannot iniitalize a variable in the same line you have declared it.

DECLARE @YesturdayDate datetime;
SET @YesturdayDate = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()-1));

select dateadd(hh,18,@YesturdayDate);

(you also have to use dateadd(hh... instead of dateadd(h... )

answered Jan 30 '13 at 09:54

Tim Schmelter

450,073
74
686
939

On 2005, it would produce 2 errors, the first of which is quite explanatory "Cannot assign a default value to a local variable". I'd have hoped the OP would mention that error if it was occurring or already resolve it. – Damien_The_Unbeliever Jan 30 '13 at 10:06

Damien_The_Unbeliever · Answer 2 · 2013-01-30T10:08:06.087

You can do it with a single DATEADD/DATEDIFF pair:

DATEADD(day,DATEDIFF(day,'20010102',GETDATE()),'2001-01-01T18:00:00')

This exploits the relationship between two constant datetime values. It's "add the whole number of days that have elapsed between 2nd January 2001 and right now to 18:00 on the 1st January 2001". Which is the same as "give me 18:00 yesterday".

As to the original error you report - if it's the only error that's occurring, the only thing I can think is that your two statements are actually in separate batches (separated from each other by a GO command)

add 18 hours to getdate() result

2 Answers2