Irfan's World

January 23, 2011

SQL Server Dates – Part 4 (Month)

Most of the times when we develop Business Intelligence reports for trending, like trend reports, cross tabs or charts, compare analysis ( current month vs. previous month, current rolling 6 months vs. previous rolling 6 months, current rolling 12 months vs. previous rolling 12 months) which are widely used in BI industry.

Following are some date examples of how to generate those dates using GETDATE() function from SQL Server 2008 database that can be beneficial for your report development. These date examples are also very useful for scheduling reports. (i.e. something we call dynamic dates as parameters for scheduling the reports)

Month Dates

select ‘First Day of the Previous Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-1,0)
select ‘Last Day of the Previous Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate()),0)-1
select ‘First Day of the Current Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate()),0)
select ‘Last Day of the Current Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1
select ‘First Day of the Next Month :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)

Rolling 12 Months

select ‘Current Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)
select ‘Current Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1
select ‘Previous Rolling 12 Months / Begin Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-23,0)
select ‘Previous Rolling 12 Months / End Date :’ , DATEADD(MM,DATEDIFF(MM,0,getdate())-11,0)-1

Wait for my next post on Weekly dates.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: