October 12, 2009 at 10:59 am
Hi Guys I' stumped, the following query is returning:
11
Sep 1 2009 12:00AM
Sep 30
Which is the range I am looking for (i.e. first and last day of prev month). I don't know why
the @Last_Day query works I found it on this site so I won't mess with success. The problem is
I am unable to convert the dates to the 112 format (i.e. 20090901 / 20090930) Can anyone tell
how I can do that? And if you can explain how the @Last_Day query works. I think what's
throwoing me is the zeros.
Declare @Last_Day varchar (7)
Declare @dd int
set @dd = DATEPART(dd,Convert(Varchar(8),Getdate(),112)) -1
Print @dd
Declare @First_Day datetime
SET @First_Day = DateADD(DAY, -@dd , Convert(Varchar(8),Getdate(),112))
SET @First_Day = DateADD(MONTH, -1, Convert(Varchar(8),@First_Day,112))
Print @First_Day
select @Last_Day = dateadd(month, datediff(month, 0, getdate()), 0) -1
Print @Last_Day
Thanks much
October 12, 2009 at 11:05 am
select convert(varchar(30),dateadd(month,datediff(month,0,getdate())-1,0),112) as FirstDayOfPreviousMonth,
convert(varchar(30),dateadd(day,-1,dateadd(month,datediff(month,0,getdate()),0)),112) as LastDayOfPreviousMonth
Just change getdate() to your date variable or column.
Here's how the last day previous month works.
The function datediff(month,0,getdate()) returns the number of months (not days, months) between the first date in the system (0) and today's date.
We then add that number of months to the first date in the system dateadd(month,datediff(month,0,getdate()),0)) which always produces the first day of the month.
In the example you gave, they simply subtracted one from that result. Using a datetime field, that equates to one day less than the first day of the month, which is of course the last day of the previous month.
In my example, I took pains to use DATEADD a second time, rather than just tacking on a -1 at the end. Because of the new date formats, simply adding -1 can't be relied on to work in all instances. Using dateadd(day,-1, ... ) will work with the new date formats, and I believe the old -1 trick is being deprecated.
Let me know if you have any questions.
Edited because I left out the convert() functions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 13, 2009 at 6:23 am
Thank you so much Bob, that makes perfect sense. Also thank you for the code it worked perfectly.
Be well
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply