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 |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2011-07-21 : 19:51:41
|
| Hi allquick question,am given a date , no of months and i need to return expiry date(date+noofmonths). easy i thought so.. DATEADD(month, noofmonths, startdate) works perfectly but i need to return 1 day less so changed it to DATEADD(dd,-1,DATEADD(month, noofmonths, startdate))but the issue dateadd returns 1 day less if resulting date does not have 31 days so in that case i dont need to remove 1 day. how do i do that ? i mean in this case..SELECT DATEADD(dd,-1,DATEADD(mm,2,'20110731'))Thanks for the advise.Cheers |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-07-21 : 20:51:15
|
| following returns "2011-09-29 00:00:00.000" but i want "2011-09-30 00:00:00.000"SELECT DATEADD(dd,-1,DATEADD(mm,2,'20110731'))Cheers |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-21 : 21:02:47
|
| DATEADD(dd, CASE WHEN DATEPART(day, startdate)<>DATEPART(day, DATEADD(month, noofmonths, startdate)) THEN 0 ELSE -1 END, DATEADD(month, noofmonths, startdate)) |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-07-21 : 22:18:41
|
| excellent robvolk, works beautifully. thank you very much. this is what am after.Cheers |
|
|
|
|
|
|
|
|
dateadd - what if month does not have 31 days!