End of month : Dateadd with Datepart MONTH



If datepart is month and the date month has more days than the return
month and the date day does not exist in the return month, the last day of the return month is returned.

Here is a sample to get month end date within a year:


 
 


;with mycte as (
select 1 as n, dateadd(year, datediff(year,0,getdate()), 0)  as Firstdt,EOMONTH(dateadd(year, datediff(year,0,getdate()), 0) )  as Enddt1
union all
Select n+1 as n, Firstdt,  EOMONTH(dateadd(month,n,Firstdt))    Enddt1 from mycte where n<12

)


select n as monthNum,Enddt1 from mycte
order by Enddt1




 

http://msdn.microsoft.com/en-us/library/ms186819.aspx

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s