May 29, 2012 at 6:15 pm
DECLARE @MyDate datetime
SET @MyDate = '2/28/2009'
SELECT DATEADD(year,-1,@MyDate)
Desired result '2/29/2008'
This below seems to work. (Subtract a year and then find the last day of the month for that date, set to midnight)
SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(year,-1,@MyDate))+1,0))), 0)
Is this a reasonable approach? I thought the SQL Server data and time functions were aware of leap year?
May 29, 2012 at 6:34 pm
The datetime functions are aware of leap years.
Your approach seems to work and seems to me to be reasonable.
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')
Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 30, 2012 at 7:44 am
dwain.c (5/29/2012)
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
Do you expect DATEADD(month, 1, '2012-02-01') to return '2012-03-03'? I certainly don't.
How about '2012-02-15' returning '2012-03-17'? Not me.
Or what about '2012-02-27' returning '2012-03-30'? Still no.
Why would '2012-02-29' return anything other than '2012-03-29'?
Why should the end of the month be treated any differently from any other day in the month? Or do you believe that the transition from counting from the beginning to counting from the end should happen at an earlier point in the month?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 30, 2012 at 8:36 am
drew.allen (5/30/2012)
dwain.c (5/29/2012)
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
Do you expect DATEADD(month, 1, '2012-02-01') to return '2012-03-03'? I certainly don't.
How about '2012-02-15' returning '2012-03-17'? Not me.
Or what about '2012-02-27' returning '2012-03-30'? Still no.
Why would '2012-02-29' return anything other than '2012-03-29'?
Why should the end of the month be treated any differently from any other day in the month? Or do you believe that the transition from counting from the beginning to counting from the end should happen at an earlier point in the month?
Drew
I'm not saying I think the results are wrong. I am saying that if you haven't gone through the checking, they may simply not return what you might expect.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 30, 2012 at 9:22 am
This is a simpler way to find the last day of the same month last year, if that is what you are after.
declare @MyDate datetime
set @MyDate = '2/28/2009'
select LastDay = dateadd(mm,datediff(mm,-1,dateadd(year,-1,@MyDate)),-1)
Results:
LastDay
-----------------------
2008-02-29 00:00:00.000
May 30, 2012 at 9:30 am
I think the following is a bit simpler:
DECLARE @MyDate DATETIME;
SET @MyDate = '2/28/2009';
SELECT DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy,-1,@MyDate)) + 1, -1);
August 27, 2015 at 9:34 am
Method-1 using
SELECT CASE WHEN DAY(DATEADD(DD,-1,'01-MAR-'+'2015'))=29 THEN 'Leap Year' ELSE 'No Leap Year' END
Result:
No Leap Year
SELECT CASE WHEN DAY(DATEADD(DD,-1,'01-MAR-'+'2016'))=29 THEN 'Leap Year' ELSE 'No Leap Year' END
Result:
Leap Year
Method-2
SELECT NAME+' '+CASE WHEN DAY(DATEADD(DD,-1,'01-MAR-'+CAST(YEAR(DOB) AS CHAR(4))))=29 THEN 'BORN IN LEAP YEAR' ELSE 'IS NOT BORN IN LEAP YEAR' END
FROM PERSON
Result:
JOHN SMITH IS NOT BORN IN LEAP YEAR
TONY CLERK BORN IN LEAP YEAR
JOHANA PERRY IS NOT BORN IN LEAP YEAR
ROLAND BABER IS NOT BORN IN LEAP YEAR
CATHY DIVER IS NOT BORN IN LEAP YEAR
August 27, 2015 at 10:30 am
I prefer just:
DECLARE @MyDate DATETIME;
SET @MyDate = '2/28/2009';
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @MyDate) - 11, -1);
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 27, 2015 at 11:56 am
drew.allen (5/30/2012)
dwain.c (5/29/2012)
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
Do you expect DATEADD(month, 1, '2012-02-01') to return '2012-03-03'? I certainly don't.
How about '2012-02-15' returning '2012-03-17'? Not me.
Or what about '2012-02-27' returning '2012-03-30'? Still no.
Why would '2012-02-29' return anything other than '2012-03-29'?
Why should the end of the month be treated any differently from any other day in the month? Or do you believe that the transition from counting from the beginning to counting from the end should happen at an earlier point in the month?
Drew
select dateadd(month,1,'2012-01-31')
LOL
I think the best plan is to know what dateadd does with this 'month' parameter, its not hard to test all cases.
August 27, 2015 at 12:21 pm
patrickmcginnis59 10839 (8/27/2015)
drew.allen (5/30/2012)
dwain.c (5/29/2012)
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
Do you expect DATEADD(month, 1, '2012-02-01') to return '2012-03-03'? I certainly don't.
How about '2012-02-15' returning '2012-03-17'? Not me.
Or what about '2012-02-27' returning '2012-03-30'? Still no.
Why would '2012-02-29' return anything other than '2012-03-29'?
Why should the end of the month be treated any differently from any other day in the month? Or do you believe that the transition from counting from the beginning to counting from the end should happen at an earlier point in the month?
Drew
select dateadd(month,1,'2012-01-31')
LOL
I think the best plan is to know what dateadd does with this 'month' parameter, its not hard to test all cases.
That's only because February doesn't have 31 days and it's changed to the last day available for that month.
There's no way to ensure that February 28th is used as the last day of the month or just the 28th day.
August 27, 2015 at 12:27 pm
Luis Cazares (8/27/2015)
patrickmcginnis59 10839 (8/27/2015)
drew.allen (5/30/2012)
dwain.c (5/29/2012)
Datetime functions do have their issues though, like the following:
SELECT DATEADD(month, 1, '2012-02-29')Depending on your point of view, the results this returns looks wrong, or maybe not. I think they're not what I'd expect.
Do you expect DATEADD(month, 1, '2012-02-01') to return '2012-03-03'? I certainly don't.
How about '2012-02-15' returning '2012-03-17'? Not me.
Or what about '2012-02-27' returning '2012-03-30'? Still no.
Why would '2012-02-29' return anything other than '2012-03-29'?
Why should the end of the month be treated any differently from any other day in the month? Or do you believe that the transition from counting from the beginning to counting from the end should happen at an earlier point in the month?
Drew
select dateadd(month,1,'2012-01-31')
LOL
I think the best plan is to know what dateadd does with this 'month' parameter, its not hard to test all cases.
That's only because February doesn't have 31 days and it's changed to the last day available for that month.
There's no way to ensure that February 28th is used as the last day of the month or just the 28th day.
I'm thinking probably the closest rule that would make sense is to add one to the "month" value then decrement the resulting day enough to make the result a valid date. However, I wouldn't venture to use that rule without testing it.
edit: make sense to ME. others results may vary 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply