[dba-SQLServer]SQL Last Full Month

To get first day of prior month, use this:

SELECT CONVERT(SMALLDATETIME, CONVERT(VARCHAR(4), DATEADD(MM,-1,GETDATE()), 
12) + '01', 112)

Not sure why 
DATEDIFF(mm,0,dateadd(mm,-1-datepart(day,getdate()),getdate())),0) returns 
'2001-08-01 00:00:00.000'

HTH
Billy


>From: "Nicholson, Karen" <knicholson at gpsx.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: RE: [dba-SQLServer]SQL Last Full Month
>Date: Wed, 24 Sep 2003 10:38:37 -0500
>
>First day of prior month:
>select DATEADD(mm,
>DATEDIFF(mm,0,dateadd(mm,-1-datepart(day,getdate()),getdate())),0)/*first
>day of prior month*/
>
>
>Here are the other ones I tripped over while trying to get to my answer, if
>anyone else finds them useful.
>
>select CAST(DATEPART(m, getdate())-1 AS int(2)) /*last month*/
>select CAST(DATEPART(yyyy, getdate())AS int(2)) /*current year*/
>select CAST(DATEPART(dd, getdate())AS int(2))    /*current day*/
>select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) /* first day of current
>year*/
>select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) /*first day of current
>month*/
>select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0)) /*last day
>of prior month*/
>select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) /*first day of the 
>quarter*/
>select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0) /*midnight for current 
>day*/
>select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0)) /*last 
>day
>of current month*/
>select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  ), 0)) /*last day
>of prior year*/
>select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))/*last 
>day
>of current year*/
>select DATEADD(mm,
>DATEDIFF(mm,0,dateadd(mm,-1-datepart(day,getdate()),getdate())),0)/*first
>day of prior month*/
>
>
>-----Original Message-----
>From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net]
>Sent: Wednesday, September 24, 2003 10:24 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: RE: [dba-SQLServer]SQL Last Full Month
>
>
>to get the last day of the month use
>
>
>select DATEADD(mm, DATEDIFF(mm,0,getdate()), -1) /* last day of month*/
>
>
>can't believe I can't get the first day of the month yet....
>
>
>
>Paul
>
>
>
>Message date : Sep 24 2003, 03:06 PM
> >From : Nicholson, Karen
>To : dba-sqlserver at databaseadvisors.com
>Copy to :
>Subject : RE: [dba-SQLServer]SQL Last Full Month
>
>
>
>
>
>
>
>
>All in
>SQL.  I just figured out how to get the first day of the
>year:
>
>
>class=687320314-24092003>
>
>select
>DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) /* first day of
>year*/
>
>
>class=687320314-24092003>
>
>So now
>I am on to trying to figure out how to get the first day of the prior month
>and
>the last day of the prior month.
>
>
>
>
>size=2>-----Original Message-----
>From: paul.hartland at fsmail.net
>[mailto:paul.hartland at fsmail.net]
>Sent: Wednesday, September 24,
>2003 10:02 AM
>To:
>dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer]SQL
>Last Full Month
>
>
>are you doing this entirely in SQL Server
>or using Access or VB as a front-end ?
>
>
>
>
>
>
>
>style="BORDER-LEFT: #ff0000 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT:
>5px">Message
>date : Sep 24 2003, 02:44 PM
> >From : Nicholson, Karen
>
>To : Dba-Sqlserver (E-mail)
>
>Copy to :
>Subject :
>[dba-SQLServer]SQL Last Full Month
>Does anyone have a quick Last Full
>Month solution in SQL? I need to run
>reports for the prior month, and
>have about 25 lines of code to get to the
>last full month. Thanks in
>advance.
>_______________________________________________
>
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>
>http://www.databaseadvisors.com
>
>
>
>
>
>
>[ (no filename) (0.2 Kb) ]
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail