Results 1 to 3 of 3
  1. #1
    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. #2
    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. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •