CODEWITHSUNDEEP
db2
user3441215
user3441215

Reputation: 11

Convert a Date field into Week Number in IBM DB2

I have a Date field (CHAR Datatype) which has values in this format: YYYYMMDD.

For example 20140729.

I want to convert it into a Weeknumber in format YYYYWKNO

For example, the result would be 201432.

How this can be done in IBM DB2?

Upvotes: 1

Views: 5325

Answers (2)

Muehe
Muehe

Reputation: 1

The following gets week 31 instead of 32. But overall I think it is a good solution for this problem:

SELECT    TO_DATE('20140801', 'YYYYMMDD') AS MYDATE
        , YEAR(TO_DATE('20140801', 'YYYYMMDD')) * 100 + WEEK(TO_DATE('20140801', 'YYYYMMDD')) AS YYYYWKNO
FROM      SYSIBM.SYSDUMMY1

Upvotes: 0

bhamby
bhamby

Reputation: 15469

Luckily for you, DB2 has some pretty good date formatting functions. Although the links for the documentation are for DB2 for Linux/Unix/Windows, it will also work on DB2 for z/OS.

You can use TIMESTAMP_FORMAT() to convert your CHAR field to an actual date, which you can then use VARCHAR_FORMAT() to format it in the way you wish:

SELECT
    VARCHAR_FORMAT(
         TIMESTAMP_FORMAT(
             '20140801'
            ,'YYYYMMDD'
         )
        ,'YYYYWW'
    )
FROM SYSIBM.SYSDUMMY1

There are two different formats for "week", one is WW, which will give the week based on a week beginning with January 1 and ending January 7, and IW, which will give the ISO Week.. Please see the documentation page for VARCHAR_FORMAT for the other formats available.

Upvotes: 4

Related Questions