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 |
|
SBLatta
Starting Member
33 Posts |
 Posted - 2010-11-16 : 13:27:38
|
| For some reason, I can't seem to wrap my head around this one:What formula would let me calculate the most recent previous September 30 from today (ie. getdate())? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-11-16 : 19:28:53
|
| If you had a Calendar table....select max(MyDate)from Calendarwhere MyDate < GetDate()and Month(MyDate) = 9and Day(MyDate) = 30You can decide how you want to handle the time portion of GetDate() when it falls on a September, 30.=======================================Man is least himself when he talks in his own person. Give him a mask, and he will tell you the truth. -Oscar Wilde, writer (1854-1900) |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-17 : 05:11:28
|
| select dateadd(month,9,DATEADD(year,datediff(year,0,getdate()),0))-1MadhivananFailing to plan is Planning to fail |
|
|
|
SBLatta
Starting Member
33 Posts |
Posted - 2010-11-17 : 10:17:50
|
quote:
Originally posted by madhivanan
select dateadd(month,9,DATEADD(year,datediff(year,0,getdate()),0))-1MadhivananFailing to plan is Planning to fail
Thanks Madhivanan, that formula worked!Let me see if I actual understand what you did:DATEDIFF(year,0,getdate()) is calculating the number of years between today and the default start date 1/1/1900 = 110 yearsDATEADD(year,110,0) is taking the 110 years from the above portion of the formula and adding it to 1/1/1900 = 1/1/2010DATEADD(month,9,1/1/2010) is adding 9 months to the above result, so 10/1/2010Finally, 1 is being subtracted from the result to give 9/30/10Is that correct? So, in SQL, adding or subtracting an integer to a date will add or subtract that number of days? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-17 : 10:20:15
|
| Yes. You are correctMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Date Calculation