| Author |
 Topic  |
|
|
Joshrinn
Posting Yak Master
118 Posts |
 Posted - 06/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/25/2012 : 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
USA
696 Posts |
Posted - 06/25/2012 : 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 month
AND datecolumn < dateadd(month, datediff(month, 0, getdate()), 0) -- first of current month
In 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
India
22461 Posts |
|
| |
Topic |
|
|
|
Date add
