[dba-SQLServer]SQL Last Full Month

[Nicholson, Karen]

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) ]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030924/f0cfedc6/attachment-0001.html