CODEWITHSUNDEEP
mysqlsqlsql-serverdatabaseleap-year
Sandeep
Sandeep

Reputation: 115

Subtracting a year with DATEADD doesn't handle leap years correctly

While subtracting a year form current date, if current day is February 28 2013 then below statement returns February 28 2012, but correct result should be February 29 2012 as 2012 is a leap year. How can this scenario be handled. 

SELECT DATEADD(year, -1, GETDATE())

Upvotes: 1

Views: 4552

Answers (3)

valex
valex

Reputation: 24144

Using your logic you should use case to process last day of the month in different way. If today is a last day of the month it will be the last day of the month one year ago. 

SELECT CASE WHEN LAST_DAY(CURDATE())=CURDATE()
            THEN LAST_DAY(DATE_SUB(CURDATE(),INTERVAL 1 YEAR)) 
            ELSE DATE_SUB(CURDATE(),INTERVAL 1 YEAR)
       END

But what you will do if today is 29.02 ? WHAT day a year ago it should be? In this case for 28.02 and 29.02 you get the same 28.02 a year ago. What logic we should use here?

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Well, here's something to think about...

SELECT CASE WHEN DAY(LAST_DAY('2012-02-28'))=29 THEN 'foo' ELSE 'bar' END x;
+------+
| x    |
+------+
| foo  |
+------+
1 row in set (1.22 sec)

SELECT CASE WHEN DAY(LAST_DAY('2013-02-28'))=29 THEN 'foo' ELSE 'bar' END x;
+------+
| x    |
+------+
| bar  |
+------+

Upvotes: 0

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57095

*Use this code *

SELECT DATE_SUB('2013-02-28', INTERVAL 365 DAY)

Upvotes: 5

Related Questions