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

db2user24 · #1 (**permalink**) 08-12-10, 18:39

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

Month = 5
DayOfWeek = 2
Week of Month = 5

Thanks!

ARWinner · #2 (**permalink**) 08-12-10, 22:58

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

db2user24 · #3 (**permalink**) 08-13-10, 00:06

thanks.. yes I forgot to mention that.. the year is given too..

tonkuma · #4 (**permalink**) 08-13-10, 09:23

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.

db2user24 · #5 (**permalink**) 08-13-10, 10:47

thanks, that definitely is quite complex.. will let you know if I come up with a simpler anwser

db2user24 · #6 (**permalink**) 08-13-10, 13:26

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!

db2user24 · #7 (**permalink**) 08-13-10, 13:27

interesting that it doesn't return the result in the same format that you get..

tonkuma · #8 (**permalink**) 08-13-10, 14:16

Quote:

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.

Quote:

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.

db2user24 · #9 (**permalink**) 08-13-10, 17:28

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech