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 |
|
Joshrinn
Posting Yak Master
118 Posts |
 Posted - 2012-06-25 : 09:16:02
|
| I was wondering how do I get this right. I did dateadd(mm,1, '04-30-2012') and it gives me '05-30-2012) but in reality in want it to auto recognize the months which has 30 days and 31 days. It should have been '05-31-2012'. How do I get this. Thanks in advance |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-25 : 10:08:22
|
quote:
Originally posted by Joshrinn
I was wondering how do I get this right. I did dateadd(mm,1, '04-30-2012') and it gives me '05-30-2012) but in reality in want it to auto recognize the months which has 30 days and 31 days. It should have been '05-31-2012'. How do I get this. Thanks in advance
You can use one or the other of the below selects. Both are really doing the same thing:DECLARE @date DATETIME = '20120430';SELECT DATEADD(mm,DATEDIFF(mm,0,@date)+2,-1);SELECT DATEADD(mm,DATEDIFF(mm,'19000101',@date)+2,'18991231'); |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-06-25 : 15:27:27
|
| To get the end of the current month: dateadd(month, datediff(month, -1, getdate()), -1)To get the end of the previous month: dateadd(month, datediff(month, -1, getdate()) - 1, -1)To filter in a where clause for the previous month:WHERE datecolumn >= dateadd(month, datediff(month, 0, getdate()) - 1, 0) -- first of previous monthAND datecolumn < dateadd(month, datediff(month, 0, getdate()), 0) -- first of current monthIn other words, don't filter on the last day of the month - filter on anything *less* than the first day of the next month.Jeff |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Date add