January 1, 2013 at 5:11 pm
Parens corrected, and my apologies. I changed to question to exclude Jan 31, this that doesn't work.
Happy New Year.
I'll award back points tomorrow.
January 1, 2013 at 6:50 pm
I think this method with the nested DATEADD/DATEDIFF is simple and has the advantage of eliminating the time part.
select
a.DT,
[LastDayOfPriorMonth] =
dateadd(mm,datediff(mm,-1,a.DT)-1,-1),
[LastDayOfPriorYear] =
dateadd(yy,datediff(yy,-1,a.DT)-1,-1)
from
( -- Test Dates
select top 32
DT = getdate()-1+
row_number() over (order by object_id)
from sys.objects
) a
order by
a.DT
Results:
DT LastDayOfPriorMonth LastDayOfPriorYear
----------------------- ----------------------- -----------------------
2013-01-01 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-02 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-03 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-04 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-05 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-06 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-07 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-08 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-09 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-10 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-11 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-12 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-13 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-14 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-15 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-16 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-17 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-18 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-19 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-20 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-21 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-22 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-23 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-24 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-25 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-26 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-27 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-28 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-29 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-30 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-01-31 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000
2013-02-01 20:47:27.733 2013-01-31 00:00:00.000 2012-12-31 00:00:00.000
January 1, 2013 at 11:14 pm
Thanks Steve
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 1, 2013 at 11:39 pm
Thanks Steve. Wish you a very happy new year.
January 2, 2013 at 12:03 am
Happy New Year to everyone!
January 2, 2013 at 12:28 am
Thanks Steve.. Great Start of the Year... Happy New Year to you and your team....
January 2, 2013 at 12:42 am
Nice one..Happy New Year to all of you......
January 2, 2013 at 2:52 am
Happy new year to all SQL serverCentral guyz..
--
Dineshbabu
Desire to learn new things..
January 2, 2013 at 2:56 am
Steve Jones - SSC Editor (1/1/2013)
I changed to question to exclude Jan 31, this that doesn't work.
Jan 30 won't work either. Jan 29 will only work in leap years.
January 2, 2013 at 7:46 am
Toreador (1/2/2013)
Steve Jones - SSC Editor (1/1/2013)
I changed to question to exclude Jan 31, this that doesn't work.Jan 30 won't work either. Jan 29 will only work in leap years.
+1
January 2, 2013 at 9:08 am
Doh! I could have sworn this worked for Jan 30. I must have been celebrating still when I looked at it yesterday.
Corrected and points awarded back.
January 2, 2013 at 9:44 am
Interesting one. Happy New Year, Steve!
January 2, 2013 at 11:29 am
Hugo,
Just testing to see if we're reading carefully? 😉
Looks as though your option 2 is screwed up somehow, returning dates years off.
SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');
..... returns 2008-12-31 00:00:00.000
January 2, 2013 at 12:07 pm
john.arnott (1/2/2013)
Hugo,Just testing to see if we're reading carefully? 😉
Looks as though your option 2 is screwed up somehow, returning dates years off.
SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');..... returns
2008-12-31 00:00:00.000
It's only the constants that are wrong. I thinkSELECT dateadd(month, datediff(month, getdate(), '20130101'), '20121231'); must be what Hugo meant.
edit: Adding 0 months to the date wanted as result is rather neat, isn't it?
Tom
January 2, 2013 at 3:48 pm
john.arnott (1/2/2013)
Hugo,Just testing to see if we're reading carefully? 😉
Looks as though your option 2 is screwed up somehow, returning dates years off.
SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');..... returns
2008-12-31 00:00:00.000
Aarrgghh!!! I know I have the habit of reversing the two arguments to the DATEDIFF function, which is why I *almost* always test it before posting. *almost* :Whistling:
Here is the correct code:
SELECT dateadd(month, datediff(month, '20110101', getdate()), '20101231');
(And Tom's correction is incorrect, though it does incidentally return the last day of the previous month in January 2013 only).
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply