Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
 Posted - 2009-08-28 : 13:12:06
|
| I Promise that this will be my LAST Date/Convert related question (at least this week :)I am tryin to retrieve these records from the table where my starting date range is the first of the month of what was 13 months ago. I have figured the logic out to calc the month and year but I keep hitting dead ends when reconstructing that back into a valid date.To get the proper month number to use I use the following:CASE WHEN datepart(month, getdate()) = 1 --- If January THEN 12 --- Force to December ELSE datepart(month, getdate()) - 1 --- Else decrement month by 1 END AS [Calc Month]And similarly for the year:CASE WHEN datepart(month, getdate()) = 1 --- If January THEN datepart(year, getdate()) - 2 --- Go back 2 years ELSE datepart(year, getdate()) -1 --- Else only go back 1 END AS [Calc year]Now assuming I want the Day to be 01, how would I take [Calc Year] + [Calc Month] + 01 to create a valid date that I can use in a where clause?John"The smoke monster is just the Others doing barbecue" |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-08-28 : 13:17:11
|
| select dateadd(mm, datediff(mm, 0, getdate())-13, 0) |
 |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-08-28 : 13:52:42
|
| jeez - I keep trying to make this much more difficult than it needs to be, aren't I?Thank you and to everyone who saved me a few days work!John"The smoke monster is just the Others doing barbecue" |
|
|
|
|
|
|
Calculating 12+ months ago