SET DATE TO 90 DAYS AGO
-
Hello,
This seems like a simple task, but need some help.
I need to set a column's date value to dates 90 days ago from today so something like:
GETDATE() - 90 DAYS
What's the easiest way to do this?
Thanks.
JN
-
I think I solved my own issue by posting here...
GETDATE() - 90 works just fine giving me 2/10/05, which is 90 days ago!
JN
-
You're better off learning this method :
Select dateadd(d, -90, Getdate())
-
If you can spare some time... why is that the chosen method, though both gives exact result including milliseconds?
Thanks.
JN
-
Because you can add many more things than days (from books online)
Syntaxe
DATEADD ( datepart , number, date )
Year : aa, aaaa
quarter : qq, q
Month : mm, m
dayofyear : ja, a
Jour : jj, j
Week : wk, ww
Hour : hh
minute : min
second : ss, s
millisecond : ms
Also if the method used to store dates change from one version to the next your substraction may yield incorrect results.
-
Don't forget that just subtracting 90 days using either method will not take you to midnight of the given day... the current time element will be included unless you do something like...
DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-90
...or...
DATEADD(dd,DATEDIFF(dd,0,GETDATE()-90),0)
...or to exemplify Remi's method...
DATEADD(dd,-90,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
Again, Remi's method would allow for other calculations very easily just by changing the first "dd" (and the value that follows) to the date part you wanted to add or subtract.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Are you really looking for 90 days or are you looking for 3 months? Just in case you're looking for the latter, subtracting 90 days is not always exact. That's where DATEADD comes in really handy, since SQL Server will automatically deal here with the potential issues.
Just my $0.02 cents anyway.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That's a pretty good two cents... lot's of folks forget that there IS a difference between 90 days and 3 months.
JN, here's the code without the time stripping part... that's what Remi meant about how useful the DATEADD function can be...
DATEADD(mm,-3,GETDATE())
Here it is with the time stripper...
DATEADD(mm,-3,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Lots of good suggestions! Thanks, guys! It did cross my mind that 90 days <> 3 months, but the person giving this spec to me wasn't too clear so I left it at 90 days, but now that I got a better suggestion, I'll change it just to be on the safe side.
Thanks, again, for your input.
JN
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply