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

1. Registered User
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. Registered User
Join Date
Jan 2003
Posts
4,310
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. Registered User
Join Date
Nov 2007
Posts
265
thanks.. yes I forgot to mention that.. the year is given too..

4. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Nov 2007
Posts
265
interesting that it doesn't return the result in the same format that you get..

8. Registered User
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. Registered User
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
•