{"id":93,"date":"2018-06-01T18:00:08","date_gmt":"2018-06-01T18:00:08","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=93"},"modified":"2018-06-17T11:50:53","modified_gmt":"2018-06-17T11:50:53","slug":"dates-in-sql-server-create-sample-date-ranges","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/01\/dates-in-sql-server-create-sample-date-ranges\/","title":{"rendered":"Dates In SQL Server: Create Sample Date Ranges"},"content":{"rendered":"

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.<\/p>\n

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:\u00a0Dates In SQL Server: Managing &#038; Manipulating Dates<\/a><\/p>\n

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.<\/p>\n

Creating dates from a table<\/h1>\n

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.<\/p>\n

In this example I’m using sys.objects<\/code>, but any large table can be used.<\/p>\n

--------------------------------------------------------------------------------\r\n-- Custom Date Range\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateStart DATE = GetDate();\r\nDECLARE @DateEnd   DATE = GetDate() + 3;\r\n \r\n\/*\r\nDECLARE @DateStart DATE = '01-JAN-14';\r\nDECLARE @DateEnd   DATE = '01-JAN-19'; -- At time of writing this is the future\r\n*\/\r\n \r\nPRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);\r\nPRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);\r\n \r\nWITH date_list AS (\r\nSELECT DateAdd(DAY,\r\n               -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 1)),\r\n               @DateStart\r\n              ) AS TheDate\r\n  FROM sys.all_objects\r\n)\r\nSELECT CAST(TheDate AS DATE) AS TheDate\r\n  FROM date_list\r\n WHERE CAST(TheDate AS DATE)\r\n        BETWEEN @DateStart\r\n            AND @DateEnd\r\n ORDER BY TheDate\r\n;<\/pre>\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t\n\t
TheDate<\/th>\n<\/tr>\n<\/thead>\n
2014-01-01<\/td>\n<\/tr>\n
2014-01-02<\/td>\n<\/tr>\n
2014-01-03<\/td>\n<\/tr>\n
2014-01-04<\/td>\n<\/tr>\n
...<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
Note:<\/strong> 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.<\/p>\n
Note:<\/strong> The logic above didn’t work in SQL Fiddle when I tried it. It did work in SSMS.<\/p>\n
SQL Fiddle Examples<\/h2>\n
Because the logic above didn’t work in SQL Fiddle, I’ve created an alternative example. Have a look at this SQL Fiddle example<\/a> for a variation useful for SQL Fiddle demos.<\/p>\n
Creating dates from a\u00a0number sequence<\/h1>\n
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.<\/p>\n
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.<\/p>\n
What is a number sequence<\/h2>\n
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.<\/p>\n
For this sequence, I create a dataset with numbers 0 – 9, and then cartesian join that dataset to itself n<\/em> times. This will create all numbers between 0<\/code> and (10<\/code>n<\/code><\/sup>)-1<\/code>. So, join the number table 3<\/em> times times for 0 - 999<\/code>. See the below code example, I’ve made it deliberately verbose in nature to demonstrate the idea.<\/p>\n
--------------------------------------------------------------------------------\r\n-- Create Nuber Lis\r\n--------------------------------------------------------------------------------\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n)\r\nSELECT *\r\n  FROM number_sequences\r\n ORDER BY concatenate_calculation\r\n;<\/pre>\n
Using the number sequence<\/h2>\n
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).<\/p>\n
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.<\/p>\n
--------------------------------------------------------------------------------\r\n-- Create Date list\r\n--------------------------------------------------------------------------------\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n)\r\nSELECT t1a,\r\n       t2a,\r\n       t3a,\r\n       concatenate_calculation,\r\n       CAST(GetDate() AS DATE)                                                 AS todays_date_SQL_Server,\r\n       CAST(DateAdd(DAY, concatenate_calculation, GetDate()) AS DATE)          AS\u00a0calculated_date_range_SQL_Server,\r\n     \/*CURDATE()                                                               AS todays_date_MySQL,\r\n       CAST(Date_Add(CURDATE(), INTERVAL concatenate_calculation DAY) AS DATE) AS calculated_date_range_MySQL *\/\r\n  FROM number_sequences\r\n ORDER BY concatenate_calculation\r\n;<\/pre>\n
Examples<\/h2>\n
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:<\/p>\n