Miscellaneous

How to create a Date Series in DB2 (DBA DB2)

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.

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *