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)

Soundex – Overcome Spelling Issues

Spelling is a big issue of mine, I just can’t do it! The Soundex built in SQL function helps overcome any spelling issues by assigning a value to a varchar depending on the phonetics of the word.

For instance, people are constantly spelling my name incorrectly.. Zac, Zack, Zak.. it’s Zach!!

Passing any of the above into the soundex function, will all return a soundex value of “Z200″:

SELECT SOUNDEX('Zach')

This means that if someone emails me asking about an account for Zak Stagers, I can use the below SQL to find the correctly spelled account:

SELECT FirstName
 
FROM Users
 
WHERE SOUNDEX(FirstName) = SOUNDEX('Zak')

The above will return all accounts that have a Soundex value of Z200, allowing me to find the correct account even without the correct spelling

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)