Useful SQL Queries to Retrieve Daterange Dates

Most of us who have worked with SQL knows of GetDate(), it returns the timestamp of the very moment it executed. Combine that with dateadd function and date calculations become much more powerful.

dateadd can take a date, and pick a part of a date (like day, month, year) and do arithmetics to it in order to get another desired date.

Example: dateadd(dd, -1, getDate()) returns yesterday, dateadd(yy, 1, getDate()) returns the same day of next year.

Last week I need to make a report that returns result of last week, last month and year to date. To make things complicated, last week doesn't mean 7 days ago, but actually Monday last week to Sunday last week. Same with last month (1st of Sept to 30th of Sept if the report is run today), to fetch the fromDate and the toDate for those case scenarios, we need to combine dateadd with datediff:

Datediff returns the integer difference for the 2 specified dates and the datepart specified.

Example: datediff(dd, getDate(), dateadd(dd, 1, getDate())) will return 1

Now, with both datediff and dateadd in action:

Dateadd(wk, Datediff(wk, 6, getDate())-1, 7) returns the first date of last week at 0 hour, 0 minute, 0.000 seconds

Dateadd(month, Datediff(month, 0, getDate())-1, 0) returns the first date of last month at 0 hour, 0 minute, 0.000 seconds

Dateadd(year, Datediff(year, 0, getDate())-1, 0) returns the first date of last year at 0 hour, 0 minute, and 0.000 seconds

From there we can be lazy about calculating the last date of last week:

Dateadd(wk, Datediff(wk, 6, getDate()), 7) actually returns the first date of this week at 0 hour, 0 minute, 0.000 seconds, if we run a query with '<' instead of '<=' then this will work just fine.

To complete the queries:

Dateadd(month, Datediff(month, 0, getDate()), 0) returns the first date of this month at 0 hour, 0 minute, 0.000 seconds

Dateadd(year, Datediff(year, 0, getDate()), 0) returns the first date of this year at 0 hour, 0 minute, and 0.000 seconds

Handling multi value parameters in Reporting Services 2008 R2

In reporting services, one can set up a multiple value parameter as a dropdown list with check boxes. User can then select the desired values as search parameter for the report. Such a parameter will look like as follows:

   

However, for an SQL stored procedure that expects a string as parameter for provider, how would a multivalue selection be handled? Multivalue selection enters the stored procedure as comma separated string. However, if we try the following: Select * from fruits where name = 'apple, orange, banana' You are not going to get much results. So we need a way to split the comma separated strings. An example of a split function looks like this:


This function returns a table with each comma separated value as individual row. So the query of fruit selection becomes the following: select * from fruits where name in (select Token from dbo.split('apple, orange, banana'))