<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.2920.0" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=953443214-24092003>First
day of prior month:</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=953443214-24092003>select
DATEADD(mm,
DATEDIFF(mm,0,dateadd(mm,-1-datepart(day,getdate()),getdate())),0)/*first day of
prior month*/ <BR></SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2></FONT> </DIV>
<DIV><FONT size=2><FONT color=#0000ff><FONT face=Arial><SPAN
class=953443214-24092003>Here are the other ones I tripped over while trying to
get to my answer, if anyone else finds them
useful.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT size=2><FONT color=#0000ff><FONT face=Arial><SPAN
class=953443214-24092003></SPAN><BR>select CAST(DATEPART(m, getdate())-1 AS
int(2)) /*last month*/<BR>select CAST(DATEPART(yyyy, getdate())AS int(2))
/*current year*/<BR>select CAST(DATEPART(dd, getdate())AS
int(2)) /*current day*/<BR>select DATEADD(yy,
DATEDIFF(yy,0,getdate()), 0) /* first day of current year*/<BR>select
DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) /*first day of current
month*/<BR>select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))
/*last day of prior month*/<BR>select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
/*first day of the quarter*/<BR>select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
/*midnight for current day*/<BR>select dateadd(ms,-3,DATEADD(mm,
DATEDIFF(m,0,getdate() )+1, 0)) /*last day of current month*/<BR>select
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)) /*last day of
prior year*/<BR>select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()
)+1, 0))/*last day of current year*/<BR>select DATEADD(mm,
DATEDIFF(mm,0,dateadd(mm,-1-datepart(day,getdate()),getdate())),0)/*first day of
prior month*/ <BR></DIV></FONT></FONT></FONT>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> paul.hartland@fsmail.net
[mailto:paul.hartland@fsmail.net]<BR><B>Sent:</B> Wednesday, September 24,
2003 10:24 AM<BR><B>To:</B>
dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> Re: RE:
[dba-SQLServer]SQL Last Full Month<BR><BR></DIV></FONT>to get the last
day of the month use<BR><BR>
<DIV><FONT color=#000000 face=Arial size=2><SPAN
class=687320314-24092003>select DATEADD(mm, DATEDIFF(mm,0,getdate()), -1) /*
last day of month*/<BR></SPAN></FONT></DIV>
<P>can't believe I can't get the first day of the month yet....</P>
<P> </P>
<P>Paul<BR><BR></P>
<BLOCKQUOTE
style="BORDER-LEFT: #ff0000 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Message
date : Sep 24 2003, 03:06 PM <BR>From : Nicholson, Karen
<KNICHOLSON@GPSX.NET><BR>To : dba-sqlserver@databaseadvisors.com <BR>Copy to
: <BR>Subject : RE: [dba-SQLServer]SQL Last Full Month
<BR><BR><BR><BR><BR><BR>
<META content="MSHTML 5.00.2920.0" name=GENERATOR><BR><BR>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=687320314-24092003>All in <BR>SQL. I just figured out how to get
the first day of the <BR>year:</SPAN></FONT></DIV><BR>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
<br>class=687320314-24092003></SPAN></FONT> </DIV><BR>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=687320314-24092003>select <BR>DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
/* first day of <BR>year*/</SPAN></FONT></DIV><BR>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
<br>class=687320314-24092003></SPAN></FONT> </DIV><BR>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=687320314-24092003>So
now <BR>I am on to trying to figure out how to get the first day of the
prior month and <BR>the last day of the prior month.</SPAN></FONT></DIV><BR>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px"><BR>
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
<br>size=2>-----Original Message-----<BR><B>From:</B>
paul.hartland@fsmail.net
<BR>[mailto:paul.hartland@fsmail.net]<BR><B>Sent:</B> Wednesday, September
24, <BR>2003 10:02 AM<BR><B>To:</B>
<BR>dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> Re:
[dba-SQLServer]SQL <BR>Last Full Month<BR><BR></DIV></FONT>are you doing
this entirely in SQL Server <BR>or using Access or VB as a front-end
?<BR><BR><BR><BR><BR>
<BLOCKQUOTE <br>style="BORDER-LEFT: #ff0000 2px solid; MARGIN-LEFT: 5px;
PADDING-LEFT: 5px">Message <BR>date : Sep 24 2003, 02:44 PM <BR>From
: Nicholson, Karen <BR><KNICHOLSON@GPSX.NET><BR>To : Dba-Sqlserver
(E-mail) <BR><DBA-SQLSERVER@DATABASEADVISORS.COM><BR>Copy to :
<BR>Subject : <BR>[dba-SQLServer]SQL Last Full Month <BR>Does anyone
have a quick Last Full <BR>Month solution in SQL? I need to run
<BR>reports for the prior month, and <BR>have about 25 lines of code to
get to the <BR>last full month. Thanks in <BR>advance.
<BR>_______________________________________________
<BR><BR>dba-SQLServer mailing list
<BR>dba-SQLServer@databaseadvisors.com
<BR><BR>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
<BR><BR>http://www.databaseadvisors.com
<BR><BR><BR></BLOCKQUOTE></BLOCKQUOTE><BR><BR>[ (no filename) (0.2 Kb)
]</BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>