DB2 / QMF Day of Week Extract 1

[ks01]

Hello ...

I am trying to extract the day of the week from a date or timestamp field. For example, if I have 2004-06-07, I want to get the value of MONDAY.

Can anyone assist?

Thanks in advance ...

K

 

[notadba]

Hi ks01

There are two functions that you can use DAYOFWEEK and DAYOFWEEK_ISO. Each of these is virtually the same, except DAYOFWEEK views a week as Sunday - Saturday - and numbers the results that way. DAYOFWEEK_ISO uses a week of Monday to Sunday. The result set will give you a number 1-7 to identify which weekday it is. You can then use a case statement to convert this to text as per the following example:

SELECT EMPLID, EFFDT, DAYOFWEEK_ISO(EFFDT),
CASE DAYOFWEEK_ISO(EFFDT)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END
FROM X.X
FETCH FIRST X ROWS ONLY WITH UR

And the output should look like:

EMPLID EFFDT
----------- ---------- ----------- ---------
12345 2004-05-14 5 Friday
12346 2004-05-24 1 Monday
12347 2003-10-07 2 Tuesday
etc

Hope this helps

 

[ks01]

Hi ...

Thank you for your suggestion. I attempted to use your suggestion. Here is my query:

SELECT DISTINCT                    
       'ADD' AS FUNC,              
       PCR_NM,                     
       TIME_ZONE_ID,               
       VISA_INT_NUM,               
       '' AS NEW_PCR_NM,           
       '' AS NEW_TIME_ZONE_ID,     
       '' AS NEW_VISA_INT_NUM,     
       VERS_ID,                    
       CASE DAYSOFWEEK(ROW_EFF_DT) 
            WHEN 1 THEN 'MONDAY'   
            WHEN 2 THEN 'TUESDAY'  
            WHEN 3 THEN 'WEDNESDAY'
            WHEN 4 THEN 'THURSDAY' 
            WHEN 5 THEN 'FRIDAY'   
            WHEN 6 THEN 'SATURDAY' 
            WHEN 7 THEN 'SUNDAY'   
       END                         
  FROM DBA1.VB2_PCR                         
 WHERE VERS_ID = 'STAWK57'                  
    OR VERS_ID NOT IN ('BA$ELINE','HI$TORY')

However, I get an error message that states "No routine was was found with compatible arguments for FUNCTION DAYSOFWEEK." My installation uses DB2 for OS/390 and I'm using QMF V7R2.

Any suggestions?

Thank you again ...

K

 

[notadba]

Hi K,

Try 'DAYOFWEEK' - you seem to have added an errant S in there.

This works fine on OS/390 DB2 connect UDB Version 7.1, have never tried QMF.

 

[ks01]

notadba ...

Thank you for catching my typo, it worked! Thank you for your help!

Take care ...

K