Tagged: Dates

T-SQL Tuesday: Return a range of dates between 2 days

Just after Adam Machanic announced the first of the T-SQL Tuesday blog events, I was asked a question about how to return a list of all the dates between two given days.  I came across some good forum posts about how to do so, many of which utilised numbers tables.  I wanted to avoid that (nothing against them, just wanted something that was stand-alone), and figured that a CTE would be the best way to go; in fact someone had posted such a method, a little of which I have used here (if I can find the post again I’ll give them a mention in the comments!).

The following procedure takes 3 parameters: a StartDate, an EndDate and a bit flag to say if the range should be inclusive (that is, will it also return the StartDate and EndDate in the results), defaulted to 1.  A list of all the dates between the first two parameters will be returned.

The maxrecursion option has been set to 3660, giving a maximum of ten years dates to return.

create procedure dbo.GetDateRange
@StartDate datetime,
@EndDate datetime,
@Inclusive bit = 1

set nocount on;

declare @DayDiff smallint;
set @DayDiff = datediff(dd, @StartDate, @EndDate);
if @Inclusive = 0
set @DayDiff = @DayDiff – 1;

with cteRange (DateRange)
as (
select dateadd(dd, datediff(dd, 0, @EndDate) – @DayDiff, 0)
union all
select dateadd(dd, 1, DateRange)
from cteRange
where dateadd(dd, 1, DateRange) < (@EndDate + @Inclusive)
select DateRange
from cteRange
option (maxrecursion 3660);

Run the procedure using the following code:

exec dbo.GetDateRange '2009-12-07', '2009-12-12'

and the results are as shown in the image at the top of the post.

Hope some of you find this useful.  This post is part of Adam Machanic’s T-SQL Tuesday.

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I’ve found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")
returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")
returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")
returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")
returns 03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.