# Thread: How to construct a date from its parts

1. Registered User
Join Date
Nov 2010
Posts
2

## Unanswered: How to construct a date from its parts

I have a date column in my DB2 database table. I need to query the table for all records where the date is less than today AND today is before the 1st of May the year following the date value. I am not familiar enough with DB2 to know how to create the where clause for the second condition. So, I am left with:

WHERE DT_COL < CURRENT DATE AND ...

Any suggestions how to accomplish this?

(I should clarify that the 1st of May the year following... is an example. The actual month and year are variable and will be supplied to the query.)

2. Registered User
Join Date
Nov 2010
Posts
2
Here's what I came up with:

WHERE (DT_COL < CURRENT DATE) AND (CONCAT('5/1/', CHAR(YEAR(DT_COL)+1)) > CURRENT DATE)

Is there a better way?

3. Registered User
Join Date
May 2009
Posts
509
SonOfPirate, Just to show a different way, here is a formula that, when supplied a month number (1 - 12) and the number of year (1 - x), will calculate the the first day of the supplied month the number of years supplied ahead:

Code:
```WITH PARM_TAB (MM_PARM, YY_PARM)
AS (SELECT  1, 1 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT  6, 2 FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT 12, 3 FROM SYSIBM.SYSDUMMY1
)
, DATE_TAB (DATE_COL)
AS (SELECT CURRENT DATE            FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  1 MONTH -  1 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  2 MONTH +  2 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  3 MONTH -  3 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  4 MONTH +  4 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  5 MONTH -  5 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  6 MONTH +  6 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  7 MONTH -  7 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  8 MONTH +  8 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE +  9 MONTH -  9 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE + 10 MONTH + 10 DAY FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT CURRENT DATE + 11 MONTH - 11 DAY FROM SYSIBM.SYSDUMMY1
)
SELECT MM_PARM
, YY_PARM
, DATE_COL
, DATE_COL + YY_PARM YEARS + (MM_PARM - MONTH(DATE_COL) ) MONTHS- (DAY(DATE_COL) - 1) DAYS AS CALC_DATE
FROM DATE_TAB
, PARM_TAB
ORDER BY MM_PARM, DATE_COL
;

MM_PARM     YY_PARM     DATE_COL   CALC_DATE
----------- ----------- ---------- ------------
1           1 11/19/2010 01/01/2011
1           1 12/18/2010 01/01/2011
1           1 01/21/2011 01/01/2012
1           1 02/16/2011 01/01/2012
1           1 03/23/2011 01/01/2012
1           1 04/14/2011 01/01/2012
1           1 05/25/2011 01/01/2012
1           1 06/12/2011 01/01/2012
1           1 07/27/2011 01/01/2012
1           1 08/10/2011 01/01/2012
1           1 09/29/2011 01/01/2012
1           1 10/08/2011 01/01/2012
6           2 11/19/2010 06/01/2012
6           2 12/18/2010 06/01/2012
6           2 01/21/2011 06/01/2013
6           2 02/16/2011 06/01/2013
6           2 03/23/2011 06/01/2013
6           2 04/14/2011 06/01/2013
6           2 05/25/2011 06/01/2013
6           2 06/12/2011 06/01/2013
6           2 07/27/2011 06/01/2013
6           2 08/10/2011 06/01/2013
6           2 09/29/2011 06/01/2013
6           2 10/08/2011 06/01/2013
12           3 11/19/2010 12/01/2013
12           3 12/18/2010 12/01/2013
12           3 01/21/2011 12/01/2014
12           3 02/16/2011 12/01/2014
12           3 03/23/2011 12/01/2014
12           3 04/14/2011 12/01/2014
12           3 05/25/2011 12/01/2014
12           3 06/12/2011 12/01/2014
12           3 07/27/2011 12/01/2014
12           3 08/10/2011 12/01/2014
12           3 09/29/2011 12/01/2014
12           3 10/08/2011 12/01/2014```