How to construct a date from its parts

SonOfPirate · #1 (**permalink**) 11-18-10, 18:41

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.)

SonOfPirate · #2 (**permalink**) 11-19-10, 08:15

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?

Stealth_DBA · #3 (**permalink**) 11-19-10, 19:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech