Home > SQL Challenge > Beginning and End of Month in SQL Server

Beginning and End of Month in SQL Server

There are times my procedures process data on a monthly basis. The following script makes sure that the procedure processes data for last month on the 1st day of a month.

declare @sdate smalldatetime, @edate smalldatetime

— @sdate and @edate mark the BOM and EOM
if @sdate is null
begin
    select @sdate = dateadd(day, -1, convert(smalldatetime, convert(char(8), getdate(), 1)))
    select @edate = dateadd(day, 1, @sdate)
    select @sdate = dateadd(day, -1*(datepart(day, @sdate)) + 1, @sdate)
    select @sdate, @edate
end

 

image

image

 

On 01/01/2010, this is what the dates will be:

image

image

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: