Dates In SQL Server: Create Sample Date Ranges
A data set of continuous dates is useful for cursors and creating date tables. Here I look at ways to produce a list of dates.
Continuing my series of posts about dates, I wanted to post something which feeds into lots of examples and testing cases I use day to day in development. I’ll also be using this technique for some of my later demos. For something a little more introductory, have a look at my article: Dates In SQL Server: Managing & Manipulating Dates
Once you have the date range you desire, the output can go into a table. I’ll award bonus points if you put a primary key on the output table, using the date. The challenge is creating a set of rows, and then modifying a start date using that set.
Creating dates from a table
The simplest method is to take a starting date and manipulate it using the row number from a table. This way each row adds an extra number of days to the starting day than the row before it.
In this example I’m using sys.objects, but any large table can be used.
--------------------------------------------------------------------------------
-- Custom Date Range
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = GetDate();
DECLARE @DateEnd DATE = GetDate() + 3;
/*
DECLARE @DateStart DATE = '01-JAN-14';
DECLARE @DateEnd DATE = '01-JAN-19'; -- At time of writing this is the future
*/
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: ' + CONVERT(VARCHAR(10), @DateEnd, 120);
WITH date_list AS (
SELECT DateAdd(DAY,
-1 + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
@DateStart
) AS TheDate
FROM sys.all_objects
)
SELECT CAST(TheDate AS DATE) AS TheDate
FROM date_list
WHERE CAST(TheDate AS DATE)
BETWEEN @DateStart
AND @DateEnd
ORDER BY TheDate
;
| TheDate |
|---|
| 2014-01-01 |
| 2014-01-02 |
| 2014-01-03 |
| 2014-01-04 |
| ... |
Note: The number of days that you can get in your date range is limited by the number of rows in the table you use. Pick a big one if you need to create a very large date range.
Note: The logic above didn’t work in SQL Fiddle when I tried it. It did work in SSMS.
SQL Fiddle Examples
Because the logic above didn’t work in SQL Fiddle, I’ve created an alternative example. Have a look at this SQL Fiddle example for a variation useful for SQL Fiddle demos.
Creating dates from a number sequence
The alternative and more purist method is to use a number sequence. A number sequence is something I think I’ll come back to in a future post, it’s a useful way to create dummy data.
One advantage of this method is portability, this method can be used across other DBMS environments with minimal code changes. I regularly move between environments, this approach saves me some time.
What is a number sequence
A number sequence can be any series of numbers in a pattern. Here I’m talking about incrementing the number by 1 in each row. In many DBMS environments (SQL Server included) there is a row number metadata attribute which takes care of this. This method gives developers the opportunity to create rows from no existing data, and apply calculations to manipulate values.
For this sequence, I create a dataset with numbers 0 – 9, and then cartesian join that dataset to itself n times. This will create all numbers between 0 and (10n)-1. So, join the number table 3 times times for 0 - 999. See the below code example, I’ve made it deliberately verbose in nature to demonstrate the idea.
--------------------------------------------------------------------------------
-- Create Nuber Lis
--------------------------------------------------------------------------------
WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
t2.a AS t2a,
t3.a AS t3a,
(t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
FROM number_tbl t1
CROSS JOIN number_tbl t2
CROSS JOIN number_tbl t3
)
SELECT *
FROM number_sequences
ORDER BY concatenate_calculation
;
Using the number sequence
Like with the “big table” idea, we use the rows created by the number sequence to manipulate the date. Either the row number (as used from the big table), or the sequence number generated (which I’ll use here).
The below code takes the number sequence and adds the sequence number to today’s date, I’ve put in a comment to show how this can be applied to MySQL to show the near portability of the method.
--------------------------------------------------------------------------------
-- Create Date list
--------------------------------------------------------------------------------
WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
t2.a AS t2a,
t3.a AS t3a,
(t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
FROM number_tbl t1
CROSS JOIN number_tbl t2
CROSS JOIN number_tbl t3
)
SELECT t1a,
t2a,
t3a,
concatenate_calculation,
CAST(GetDate() AS DATE) AS todays_date_SQL_Server,
CAST(DateAdd(DAY, concatenate_calculation, GetDate()) AS DATE) AS calculated_date_range_SQL_Server,
/*CURDATE() AS todays_date_MySQL,
CAST(Date_Add(CURDATE(), INTERVAL concatenate_calculation DAY) AS DATE) AS calculated_date_range_MySQL */
FROM number_sequences
ORDER BY concatenate_calculation
;
Examples
I’ve put a couple of examples together as this is useful for many other cases where a developer might want to create a date sequence or range of rows dynamically:
Custom date list in SQL Server
This code takes a variable start and end date, note that for more than 999 days, extra CROSS JOIN links (and sequence number calculation) changes will be required.
--------------------------------------------------------------------------------
-- Custom Date Range
-- Using Number sequence
-- Note that this limits us to 999 days
-- To add more days extend the CROSS JOIN
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = '01-JAN-17'; -- At time of writing this is the past
DECLARE @DateEnd DATE = '01-JAN-19'; -- At time of writing this is the future
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: ' + CONVERT(VARCHAR(10), @DateEnd, 120);
WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
t2.a AS t2a,
t3.a AS t3a,
(t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
FROM number_tbl t1
CROSS JOIN number_tbl t2
CROSS JOIN number_tbl t3
)
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
FROM number_sequences
WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
ORDER BY concatenate_calculation
;
See it in action over at rextester.com
3 Replies to “Dates In SQL Server: Create Sample Date Ranges”