SQL Code to Create Date and Time Intervals Table in SQL Server
Time schedule table or date table is frequently required by sql developers during t-sql coding. In this t-sql tutorial I want to give some sql hints that SQL programmers can use in their daily works. You will see that the following sql samples use SQL Server numbers table code in order to create a periodic time blocks or time intervals for different purposes.
Please note that the SQL Server date table with time details in different examples use the basic structure of SQL numbers table and SQL Server DATEADD datetime function.
Create Date Table in SQL Server
The first sample code generate SQL date table for transact-sql developers based on SQL Server numbers table methods. The below transact-sql script will generate a sql dates table in SQL Server for year 2011.
declare @date datetime = '20100101'
SELECT
number+1 No,
dateadd(dd,number,@date) [date]
FROM master..spt_values
WHERE
Type = 'P'
AND dateadd(dd,number,@date) < dateadd(yy,1,@date)
ORDER BY Number
And the final output for the above sql date table query is as follows
Create SQL Server Date Table with Periods in Hours
Date table with hours can also be a requirement for sql developers in their SQL Server programs. Transact-SQL programmers can use the following tsql query to build SQL Server date table with hourly time periods. One advantage of the below SELECT statement is the datetime table is created on the fly without the creation of a temp table, etc.
declare @date datetime = '20100101'
SELECT
dt.number+1 DayNo,
tt.number+1 HourNo,
dateadd(dd,dt.number,@date) [date],
dateadd(hh,tt.number,dateadd(dd,dt.number,@date)) [start],
dateadd(hh,tt.number+1,dateadd(dd,dt.number,@date)) [end]
FROM master..spt_values dt, master..spt_values tt
WHERE
dt.Type = 'P' AND
tt.Type = 'P' AND
dt.number < 365 AND
tt.number < 24 AND
dateadd(dd,dt.number,@date) < dateadd(yy,1,@date)
ORDER BY dt.Number, tt.Number
As you can see, the above SQL Server date table includes time periods in hours.
Create SQL Time with 15 Minutes Period in SQL Server
What about if you need 15 minutes time blocks in your SQL Server datetime table? Perhaps the following t-sql SELECT statement might be useful for generating a SQL Server time table with 15 minutes time intervals.
declare @date datetime = '20100101'
SELECT
dt.number+1 DayNo,
tt.number+1 HourNo,
mt.number+1 Quarter,
dateadd(mi,mt.number,dateadd(hh,tt.number,dateadd(dd,dt.number,@date))) [start],
dateadd(mi,(15*mt.number)+15,dateadd(hh,tt.number,dateadd(dd,dt.number,@date))) [end]
FROM master..spt_values dt, master..spt_values tt, master..spt_values mt
WHERE
dt.type = 'P' AND
tt.type = 'P' AND
mt.type = 'P' AND
dt.number < 365 AND
tt.number < 24 AND
mt.number IN (0, 1, 2, 3) AND
dateadd(dd,dt.number,@date) < dateadd(yy,1,@date)
ORDER BY dt.Number, tt.Number, mt.Number
When the above tsql is executed, the resultant return set will include dates with hourly periods including 15 minutes time intervals.
Prepare Working Hours Table in SQL Server
Dealing with working hours is very common in sql programming by t-sql developers. It is simple to create SQL Server working hours with start time and end time information in your sql codes. Even sql programmers can set lunch time breaks into sql working hours time intervals table as shown in the below sample code.
declare @date datetime = '20110101'
SELECT
dt.number+1 DayNo,
cast(dateadd(dd,dt.number,@date) as date) [date],
dateadd(mi,wh.s_mi,dateadd(hh,wh.s_hh,dateadd(dd,dt.number,@date))) [start],
dateadd(mi,wh.e_mi,dateadd(hh,wh.e_hh,dateadd(dd,dt.number,@date))) [end]
FROM master..spt_values dt, (
select 7 s_hh, 45 s_mi, 12 e_hh, 15 e_mi
union all
select 13 s_hh, 15 s_mi, 17 e_hh, 45 e_mi
) wh
WHERE
dt.type = 'P' AND
dt.number < 365 AND
dateadd(dd,dt.number,@date) < dateadd(yy,1,@date)
ORDER BY dt.Number, wh.s_hh
Here is the return list of working time intervals in a year created by SQL Server working hours time table query.
