SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code

And the final output for the above sql date table query is as follows

SQL Server date table for tsql developers


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
Code

As you can see, the above SQL Server date table includes time periods in hours.

sql date table with 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
Code

When the above tsql is executed, the resultant return set will include dates with hourly periods including 15 minutes time intervals.

sql time table in 15 minutes period


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
Code

Here is the return list of working time intervals in a year created by SQL Server working hours time table query.

sql work hours table with time intervals



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.