How to create a Date Series in DB2

27 December,2017 by Jack Vamvas

Question: How can I create a Date Series  in DB2 LUW. e.g. 01-01-2017 to 30-01-2017 . ? 

Answer:  There are a number of different ways to create a date series in DB2 LUW.One way is the example used below, which lists the dates from 1st January 2017 - 30 January 2017. :

 

 db2 "with tempdateseries (date) as (
select date('01.01.2017') as date from sysibm.sysdummy1
union all
select date + 1 day from tempdateseries
where date < date('30.01.2017')
)   select * from tempdateseries"

This method will give you an output example such as :

01/01/2017
02/01/2017
03/01/2017
04/01/2017
05/01/2017
06/01/2017
07/01/2017
08/01/2017
09/01/2017
10/01/2017
11/01/2017
12/01/2017
13/01/2017
14/01/2017
15/01/2017
16/01/2017
17/01/2017
18/01/2017
19/01/2017
20/01/2017
21/01/2017
22/01/2017
23/01/2017
24/01/2017
25/01/2017
26/01/2017
27/01/2017
28/01/2017
29/01/2017
30/01/2017

If it's a high impact query - it may be more useful to INSERT the results into a table - where the code can reference as required.

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to create a Date Series in DB2

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright