SSRS Default Date Parameter Functions

Several months ago, I made a list of date functions I commonly used to set the default value of an SSRS Parameter. I put it on our internal network for my colleagues, and today figured I may as well publish it on my blog too.

Function Description
Today
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), Day(Now()))
1st of Current Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), 1)
1st of Last Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now())-1, 1)
1st of Current Year
(00:00:00)
=DateSerial(Year(Now()), 1, 1)
Last of Current Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now())+1,1))
Last of Last Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now()),1))

As with all things, make sure you test before implementing to ensure it does exactly as you need.

If you have a favorite you use regularly, add it to the comments and I’ll include it in the post.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Scalar Functions to Return First Last Day of Month

Last Day of the Month

Below is the syntax used to create an extremely simple scalar function to return the last day of the month for the date passed in.

CREATE FUNCTION dbo.fnLastDayOfMonth (@Date DATE)
 
RETURNS DATE
 
AS
 
BEGIN
 
     SELECT @Date = DATEADD(M, 1, DATEADD(D, -DAY(@Date), @Date))
 
     RETURN @Date
 
END

The function does take leap years into consideration, as executing the below select against it, does return “2012-02-29″.

SELECT dbo.fnLastDayOfMonth('2012-02-22')

First Day of the Month

Below is the SQL used to create a function which returns the first day of the month for the date passed in.

CREATE FUNCTION dbo.fnFirstDayOfMonth (@Date DATE)
 
RETURNS DATE
 
AS
 
BEGIN
 
     SELECT @Date = DATEADD(D, -DAY(@Date)+1, @Date)
 
     RETURN @Date
 
END
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)