First, last day prev month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

First, last day prev month

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-10-12 : 13:00:31

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-12 : 13:09:02

your convert is at the wrong place...and also you have defined @First_Day as a datetime field instead of a varchar field...

maybe this??

Declare @Last_Day varchar (8)
Declare @dd int
set @dd = DATEPART(dd,Convert(Varchar(8),Getdate(),112)) -1
Print @dd

Declare @First_Day varchar(8)
SET @First_Day = convert(varchar(8),DateADD(DAY, -@dd , getdate()),112)
SET @First_Day = convert(varchar(8),DateADD(MONTH, -1, @First_Day),112)
Print @First_Day

select @Last_Day = convert(varchar(8),dateadd(month, datediff(month, 0, getdate()), 0) -1,112)
Print @Last_Day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources