Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: Form a date based on month, day of week and day of month

    Question -- how can you form the date 5/31/2010 based on this information ?

    Month = 5
    DayOfWeek = 2
    Week of Month = 5


    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Unless you know the year, you cannot. If you do know the year, then you can proceed using the first of the month as a base.

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks.. yes I forgot to mention that.. the year is given too..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This may be an answer.
    But, it is rather complex expressions.
    So, I want to find simpler expression.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT year_ , month_ , dayofweek , weekofmonth
         , (SELECT firstday + ( weekofmonth * 7 - MOD( DAYOFWEEK(firstday) - dayofweek + 6 , 7 ) - 1 ) DAYs
              FROM LATERAL(VALUES DATE('0001-01-01') + (year_ - 1) YEARs + (month_ - 1) MONTHs) m(firstday)
           ) calculated_date
      FROM (VALUES ( 2010 ,  5 , 2 , 5 )
                 , ( 2010 ,  7 , 5 , 1 )
                 , ( 2010 ,  7 , 7 , 1 )
                 , ( 2010 ,  7 , 1 , 1 )
                 , ( 2010 ,  7 , 4 , 1 )
                 , ( 2010 , 10 , 2 , 2 )
           ) d( year_ , month_ , dayofweek , weekofmonth )
    ;
    ------------------------------------------------------------------------------
    
    YEAR_       MONTH_      DAYOFWEEK   WEEKOFMONTH CALCULATED_DATE
    ----------- ----------- ----------- ----------- ---------------
           2010           5           2           5 2010-05-31     
           2010           7           5           1 2010-07-01     
           2010           7           7           1 2010-07-03     
           2010           7           1           1 2010-07-04     
           2010           7           4           1 2010-07-07     
           2010          10           2           2 2010-10-11     
    
      6 record(s) selected.

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    thanks, that definitely is quite complex.. will let you know if I come up with a simpler anwser

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    I tried it out and I get this --
    db2 "SELECT (SELECT firstday + ( weekofmonth * 7 - MOD( DAYOFWEEK(firstday) - dayofweek + 6 , 7 ) - 1 ) DAYs FROM LATERAL(VALUES DATE('0001-01-01') + (year_ - 1) YEARs + (month_ - 1) MONTHs) m(firstday) ) calculated_date FROM (VALUES ( 2010 , 10 , 2 , 2 ) ) d( year_ , month_ , dayofweek , weekofmonth )"

    CALCULATED_DATE
    ---------------
    10/11/2010

    How can I get result returned in the format -- 2010-10-11-00.00.00.000000

    thanks!

  7. #7
    Join Date
    Nov 2007
    Posts
    265
    interesting that it doesn't return the result in the same format that you get..

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    interesting that it doesn't return the result in the same format that you get..
    The display format of date data type depends on locale.

    How can I get result returned in the format -- 2010-10-11-00.00.00.000000
    TIMESTAMP(calculated_date)

    Here is an example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     calculate AS (
    SELECT year_ , month_ , dayofweek , weekofmonth
         , (SELECT first_day + ( weekofmonth * 7 - MOD( DAYOFWEEK(first_day) - dayofweek + 6 , 7 ) - 1 ) DAYs
              FROM LATERAL(VALUES DATE('0001-01-01') + (year_ - 1) YEARs + (month_ - 1) MONTHs ) m(first_day)
           ) calculated_date
      FROM (VALUES ( 2010 ,  5 , 2 , 5 )
                 , ( 2010 ,  7 , 5 , 1 )
                 , ( 2010 ,  7 , 7 , 1 )
                 , ( 2010 ,  7 , 1 , 1 )
                 , ( 2010 ,  7 , 4 , 1 )
                 , ( 2010 , 10 , 2 , 2 )
           ) d( year_ , month_ , dayofweek , weekofmonth )
    )
    SELECT year_ , month_ , dayofweek , weekofmonth
         , calculated_date             AS date_local
         , CHAR(calculated_date , USA) AS date_usa
         , CHAR(calculated_date , ISO) AS date_iso
         , TIMESTAMP(calculated_date)  AS format_timestamp
      FROM calculate
    ;
    ------------------------------------------------------------------------------
    
    YEAR_       MONTH_      DAYOFWEEK   WEEKOFMONTH DATE_LOCAL DATE_USA   DATE_ISO   FORMAT_TIMESTAMP   
    ----------- ----------- ----------- ----------- ---------- ---------- ---------- -------------------
           2010           5           2           5 2010-05-31 05/31/2010 2010-05-31 2010-05-31-00.00.00
           2010           7           5           1 2010-07-01 07/01/2010 2010-07-01 2010-07-01-00.00.00
           2010           7           7           1 2010-07-03 07/03/2010 2010-07-03 2010-07-03-00.00.00
           2010           7           1           1 2010-07-04 07/04/2010 2010-07-04 2010-07-04-00.00.00
           2010           7           4           1 2010-07-07 07/07/2010 2010-07-07 2010-07-07-00.00.00
           2010          10           2           2 2010-10-11 10/11/2010 2010-10-11 2010-10-11-00.00.00
    
      6 record(s) selected.

  9. #9
    Join Date
    Nov 2007
    Posts
    265
    thanks, i actually used your command like this and it gives me what I want --

    SELECT TIMESTAMP(DATE((SELECT firstday + ( nknthdow * 7 - MOD( DAYOFWEEK(firstday) - nkdow + 6 , 7 ) - 1 ) DAYs FROM
    LATERAL(VALUES DATE('0001-01-01') + (nkyear - 1) YEARs + (nkmonth - 1) MONTHs) m(firstday) )) , TIME('00.00.00')) FROM SYSIBM.SYSDUMMY1


    Another question I have is -- do you know if there's any way to create a SQL based function in DB2 that takes 'Month', 'Day', 'Year' and 'Timezone' as parameters and returns the time with the correct gmt offset?

    I don't think that DB2 is very timezone oriented so not sure if I can achieve this.. for example, let's call the function GET_OFFSET.. it will have 4 parameters

    GET_OFFSET ( month INTEGER, day INTEGER, year INTEGER, timezone VARCHAR(64))

    --> example : month = 7, day = 1, year =2010, timezone = 'US/Central'
    --> result returned = 2010-07-01-05.00.00.000000 since the GMT offset is 5 hours

    thanks!

Posting Permissions

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