How to Create a Date Table or a SQL Server Calendar Table using CTE T-SQL Code
In this sql article, I'd like to share the t-sql codes for creating dates sql table having date records between a given date range.
I believe many sql developers or sql administrators have built at least once such a sql date table or a sql calendar table.
Because by creating dates table, you can use it in your sql codes for displaying missing date values in a database table for example which is a necessity for many cases.
Or if you are using SQL Server Analysis Services to build OLAP Cubes etc., you may need to build again date tables for managing datetime dimensions, etc.
I will deal on two major topics of how to create date tables. One method is building an on the fly date table which is temporary. The second method is building a permanent date table.
If you know you are going to frequently use dates table in many solutions of different sql problems, then I strongly suggest you create a permanent date table in your database with a wide range of dates according to your needs.
Creating SQL Dates Table using T-SQL CTE (Common Table Expression)
SQL developers will know the CTE (Common Table Expression) improvement in T-SQL with Microsoft SQL Server 2005.
I will code a sql select script which uses CTE enhancement to build a dates table on the fly.
For more samples on T-SQL Common Table Expression CTE you can view MS SQL Server Recursive T-SQL Sample Split Function or Create a Numbers Table in MS SQL Server 2005 or SQL2008 Databases.
Here is the sample sql code for a Calendar Table in SQL between date ranges 06/01/2009 and 06/30/2009.
WITH CTE_DatesTable
AS
(
SELECT CAST('20090601' as datetime) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= '20090630'
)
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
The above CTE expression includes the option MAXRECURSION which is set to 0 meaning to indefinite loop is allowed.
If we do not set the OPTION (MAXRECURSION 0), then if we run the above date table sql query for a date range more than 100 days, then we will have an sql exception like below:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
You can refer to the following post for OPTION MAXRECURSION error : The maximum recursion 100 has been exhausted before statement completion..
You can improve the above t-sql cte code in order to execute it with parameters identifying the beginning date and ending date of the date range.
Or if you are going to run this date range query against a table with date columns in it, you can select the minimum date or the maximum date using MIN() and MAX() aggregate functions over the related sql date column.
You can even place the sql date table script into a user defined function and make the select query parameterized as follows:
CREATE FUNCTION [dbo].[DateTable]
(
@FirstDate datetime,
@LastDate datetime
)
RETURNS @datetable TABLE (
[date] datetime
)
AS
BEGIN
SELECT @FirstDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @FirstDate));
SELECT @LastDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @LastDate));
WITH CTE_DatesTable
AS
(
SELECT @FirstDate AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= @LastDate
)
INSERT INTO @datetable ([date])
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0)
RETURN
END
In the above t-sql user defined function for creating a sql calendar table, I have used the DATEADD() and DATEDIFF() datetime functions in order to set the first date an last date parameters to show only date values with hour, minute and seconds with 0 values.
If you are using MS SQL Server 2008, you know you can now use date data type instead of using datetime data type for only dates.
New data types like date, time, etc are very handy in such situations.
You can reference to New Data Types for SQL Developers and Administrators in SQL Server 2008.
Let's create a new the DatesTable function with the new Date Data Type:
CREATE FUNCTION [dbo].[DatesTable]
(
@FirstDate date,
@LastDate date
)
RETURNS @datetable TABLE (
[date] date
)
AS
BEGIN
WITH CTE_DatesTable
AS
(
SELECT @FirstDate AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [date]) <= @LastDate
)
INSERT INTO @datetable ([date])
SELECT [date] FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
RETURN;
END
Here how you can use the DateTable function and the DatesTable function which return a temporary dates table in your t-sql scripts code as follows :
SELECT [date]
FROM [dbo].[DateTable](GETDATE(), DATEADD(dd,5,GETDATE()))
SELECT [date]
FROM [dbo].[DatesTable](GETDATE(), DATEADD(dd,5,GETDATE()))
If you want to build a permanent dates table, first create your dates table. I gave it a name SQLDatesTable.
create table SQLDatesTable
(
id int identity(1,1) not null,
[date] datetime not null
)
Then you can use a SELECT .. INTO .. FROM .. syntax or INSERT INTO ... SELECT .. FROM ... syntax using the above DateTable user-defined functions (udf) or even using a while loop or a sql cursor.
insert into SQLDatesTable ([date])
select [date] from [dbo].[DateTable]('20080101', '20081231')
-- Or
declare @i int = 0, @date datetime = '20090101'
while @i <= 100
begin
insert into SQLDatesTable ([date]) values (dateadd(dd,@i,@date))
set @i = @i + 1
end
I know there is a lot of ways of building a date table. And the solutions will change according to the database version or edition you are using.
You are free to use any of the above scripts during your development and feel free to contact me in any case you want to comment on scripts.
If you want to build a sql numbers table you can read the article and use the T-SQL scripts at Create a Numbers Table in MS SQL Server 2005 or SQL2008 Databases.
Below is a last example of SQL calendar table script which displays the current month calendar.
SELECT
[date],
DATENAME(day, [date]) + ' ' + DATENAME(month, [date]) + ', ' + DATENAME(DW, [date])
FROM [dbo].[DatesTable]( -- current month calendar table
DATEADD(dd,1,EOMONTH (GETDATE(), -1)),
EOMONTH (GETDATE())
)
Please note that besides using SQL dates table function, I've also used SQL EMonth() function for defining the first and last date of the current month.