Midnight three years ago 2

[ter79]

Can someone help me with creating a dateadd statment that will return today's date three ago and at 11:59 PM

12/19/2002 23:59:59 is the final result

I have the syntax for the year part but not to get it to 23:59:59

SELECT DateAdd(yyyy, -3, CONVERT(smalldatetime, CONVERT(char(10), GETDATE(), 103), 103))

 

[gmmastros]

You need to use DateTime not SmallDateTime. Then, simply add 86399 seconds (because there are 86400 seconds in a day).

SELECT DateAdd(Second, 86399, DateAdd(yyyy, -3, CONVERT(datetime, CONVERT(char(10), GETDATE(), 103), 103)))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Same thing, another way:

select dateadd(ss, -1, dateadd(yy, -3, dateadd(dd, datediff(dd, 0, getdate()), 1)))

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[ter79]

Thanks gmmastros and vongrunt