Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

T-SQL: Generate a list of dates

Sometimes we can be working and we write or find a ridiculously simple bit of code that we know will be useful in the future, then we just go ahead and plain forget all about it. When I find such stuff I like to stick it on my blog so that I know where to find it in the future and also on the off chance that someone else might find it useful. Here is one such example.

The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.

    1 with mycte as

    2 (

    3     select cast('1900-01-01' as datetime) DateValue

    4     union all

    5     select DateValue + 1

    6     from    mycte   

    7     where   DateValue + 1 < '2050-12-31'

    8 )

    9 select DateValue

   10 from    mycte

   11 OPTION (MAXRECURSION 0)

Nice 'n' easy! If you want a different date range simply change the dates in the query. Of course, you can easily extend this to produce all the attributes of a day that you may require in a Time dimension table - maybe/hopefully someone else might do that for me.

Before CTEs turned up in SQL Server 2005 you would have needed procedural code to do this.

 

By the way, I came up with this code after my colleague James Pipe found the equivalent code for Oracle. He's promised that his first ever blog post will be on this subject (I'm trying to goad him into writing it).

-Jamie

N.B. I used J.T. Leigh's excellent Copy Source as HTML tool in order to write this blog post.

 

UPDATE: James has now posted his like-for-like posting. You can see it here: http://blogs.conchango.com/tags/List+of+dates/default.aspx

 

Published Thursday, January 11, 2007 10:44 PM by jamie.thomson

Comments

 

Mobin Mohammed said:

You did the hard work Jamie.

I just modified your select statement to return extra columns for a time dimension

select DateValue,

Year(DateValue) as Year,  

DATEPART(Quarter ,DateValue) as Quarter,

DATENAME( month,DateValue) as MonthName,

DATEPART(m ,DateValue)  as MonthNo,

day (DateValue) as Day

from    mycte

OPTION (MAXRECURSION 0)

January 12, 2007 4:58 PM
 

chenghong said:

Here is a way to do the same thing in SQL Server 2000 and before:

http://blogs.x2line.com/al/articles/207.aspx

January 14, 2007 8:26 AM
 

Jim 2.0 said:

I have over the last couple of days been working with Jamie Thomson on a sql query to return a daily

January 15, 2007 4:59 PM
 

jamie.thomson said:

Mobin,

I knew someone would take that up :)

Thanks

Jamie

January 15, 2007 6:19 PM
 

Andrey Makarov said:

Are you using it as a source for time dimension, are you? =)

January 22, 2007 9:45 AM
 

Carina Jones said:

I had'nt looked in to CTEs before I found your post. This is excellent!! Thanks.  (I needed to create a calendar for my time dimension. The values, names of days etc need to be in Swedish date format, which ruled out using the Server based calendar. This really simplifies life for me.)

January 26, 2007 1:17 PM
 

Rob Bertora said:

And for those poor suckers that don't have sql2005:

You can use a temporary memory table in sql2000:

DECLARE @myTable     TABLE (

                    TheDate datetime

                   )

declare @StartDate datetime

declare @Days int

declare @CurrentDay int

set @StartDate = '1/1/2006'

set @Days = 10

set @CurrentDay = 0

while @CurrentDay < @Days

begin

insert @myTable (TheDate) values (dateadd(dd, @CurrentDay, @StartDate))

set @CurrentDay = @CurrentDay + 1

end

select * from @myTable

April 3, 2007 12:27 PM
 

Jim 2.0 said:

After experimentation with various datepart and datename methods following my most recent blog post ,

April 24, 2007 3:59 PM
 

jaya said:

How to generate Date in SQL

June 27, 2007 5:27 PM
 

Slade Hornick said:

Awesome! work.  I used this today to create a table of our "working days" that doesn't inlcude weekends and various holidays.

September 22, 2007 9:21 PM
 

Pete said:

I love you thanks for this!

October 19, 2007 7:26 PM
 

Rajan said:

Great... Thank you for sharing...

October 29, 2007 3:55 PM
 

Stinger said:

It's really great!

But dose someone use this dates list for source of Time Dimension is Analysis Services?

November 15, 2007 1:26 PM
 

chris said:

thanks rob

November 29, 2007 7:43 PM
 

leon said:

Nice one, thanks. I was struggling to try and figure this out myself.

December 13, 2007 2:32 PM
 

Nitin Badole said:

Many thanks for posting this entry. I was looking exactly for this.

February 22, 2008 9:54 AM
 

Rahul Chawhan said:

I need to create a Time Dimension Table with the following details.

The FullDateAlternateKey has to generated with date from 1998-01-01 to 2008-31-12. Kindly help me with this. Thanks in advance

Column Name DataType Length Nullable

FullDateAlternateKey datetime

DayNumberOfWeek tinyint Y

EnglishDayNameOfWeek nvarchar 10 Y

DayNumberOfMonth tinyint Y

DayNumberOfYear smallint Y

WeekNumberOfYear tinyint Y

EnglishMonthName nvarchar 10 Y

MonthNumberOfYear tinyint Y

CalenderQuarter tinyint Y

CalenderYear char 4 Y

CalenderSemester tinyint Y

FiscalQuarter tinyint Y

FiscalYear char 4 Y

FiscalSemester tinyint Y

April 3, 2008 12:00 PM
 

jamie.thomson said:

Rahul,

There's plenty of information in this blog entry to get you started with this. Additionally you should check out the DATEPART function, and other T-SQL functions that operate on dates.

-Jamie

April 3, 2008 12:15 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems