DB2 - L

Hello.  I hope you are well.

I have a need to convert a CIMS DB2 date into string.  I've been able to use the to_char function on some pieces, but am having difficulties with the following two items.  I'm not sure if the capability exists.

1. Output the day of month with the ordinal suffix (st/nd/rd/th).  For example, 2021-02-01 output as 1st and 2021-01-28 is returned as 28th.

SELECT to_char(DT_GREGORIAN, 'FMDDth')   

FROM Table           

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING FMDDth IS NOT VALID FOR THE

TO_CHAR FUNCTION                                                

1. Output the date as DD of Month, like "1st of January" and "28th of September".  In this case, I am able to output day and month in the desired format, but not with the word 'or' in the middle'.

SELECT to_char(DT_GREGORIAN, 'DD "of" Month')   

FROM Table    

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING DD "of" Month IS NOT VALID

         FOR THE TO_CHAR FUNCTION                                         

This SQL works

SELECT to_char(DT_GREGORIAN,'DD Month')

FROM Table            

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                     

---------+---------+---------+---------+------

13 January                                   

Hello.  I hope you are well.

I have a need to convert a date into string.  I've been able to use the to_char function on some pieces, but am having difficulties with the following two items.  I'm not sure if the capability exists.

1. Output the day of month with the ordinal suffix (st/nd/rd/th).  For example, 2021-02-01 output as 1st and 2021-01-28 is returned as 28th.

SELECT to_char(DT_GREGORIAN, 'FMDDth')   

FROM Table           

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING FMDDth IS NOT VALID FOR THE

TO_CHAR FUNCTION                                                

1. Output the date as DD of Month, like "1st of January" and "28th of September".  In this case, I am able to output day and month in the desired format, but not with the word 'or' in the middle'.

SELECT to_char(DT_GREGORIAN, 'DD "of" Month')   

FROM Table    

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING DD "of" Month IS NOT VALID

         FOR THE TO_CHAR FUNCTION                                         

This SQL works

SELECT to_char(DT_GREGORIAN,'DD Month')

FROM Table            

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                     

---------+---------+---------+---------+------

13 January                                   

with dts as ( 
select distinct date(statstime) dt 
from sysibm.sysindexes 
) 
select dt 
      ,strip(strip(char(day(dt))) concat 
        substr('th st nd rd ' 
        ,int(substr('1230000000000000000012300000001',day(dt), 1))
        *3+1, 3) concat 'of ' Concat 
        strip(substr( 
        'January February March April May June ' 
        concat 
        'July August SeptemberOctober November December ' 
        ,month(dt)*9-8 , 9))) datestring 
from dts 
fetch first 100 rows only with ur; 
---------+---------+---------+---------+---------+---------+---------
DT DATESTRING 
---------+---------+---------+---------+---------+---------+---------
01.01.0001 1st of January 
23.04.1998 23rd of April 
13.05.1998 13th of May 
17.05.1998 17th of May 
23.06.1998 23rd of June 
18.02.2000 18th of February 
11.01.2001 11th of January 
21.01.2003 21st of January 

Hello.  I hope you are well.

I have a need to convert a date into string.  I've been able to use the to_char function on some pieces, but am having difficulties with the following two items.  I'm not sure if the capability exists.

1. Output the day of month with the ordinal suffix (st/nd/rd/th).  For example, 2021-02-01 output as 1st and 2021-01-28 is returned as 28th.

SELECT to_char(DT_GREGORIAN, 'FMDDth')   

FROM Table           

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING FMDDth IS NOT VALID FOR THE

TO_CHAR FUNCTION                                                

1. Output the date as DD of Month, like "1st of January" and "28th of September".  In this case, I am able to output day and month in the desired format, but not with the word 'or' in the middle'.

SELECT to_char(DT_GREGORIAN, 'DD "of" Month')   

FROM Table    

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING DD "of" Month IS NOT VALID

         FOR THE TO_CHAR FUNCTION                                         

This SQL works

SELECT to_char(DT_GREGORIAN,'DD Month')

FROM Table            

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                     

---------+---------+---------+---------+------

13 January                                   

with dts as ( 
select distinct date(statstime) dt 
from sysibm.sysindexes 
) 
select dt 
      ,strip(strip(char(day(dt))) concat 
        substr('th st nd rd ' 
        ,int(substr('1230000000000000000012300000001',day(dt), 1))
        *3+1, 3) concat 'of ' Concat 
        strip(substr( 
        'January February March April May June ' 
        concat 
        'July August SeptemberOctober November December ' 
        ,month(dt)*9-8 , 9))) datestring 
from dts 
fetch first 100 rows only with ur; 
---------+---------+---------+---------+---------+---------+---------
DT DATESTRING 
---------+---------+---------+---------+---------+---------+---------
01.01.0001 1st of January 
23.04.1998 23rd of April 
13.05.1998 13th of May 
17.05.1998 17th of May 
23.06.1998 23rd of June 
18.02.2000 18th of February 
11.01.2001 11th of January 
21.01.2003 21st of January 

Hello.  I hope you are well.

I have a need to convert a date into string.  I've been able to use the to_char function on some pieces, but am having difficulties with the following two items.  I'm not sure if the capability exists.

1. Output the day of month with the ordinal suffix (st/nd/rd/th).  For example, 2021-02-01 output as 1st and 2021-01-28 is returned as 28th.

SELECT to_char(DT_GREGORIAN, 'FMDDth')   

FROM Table           

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING FMDDth IS NOT VALID FOR THE

TO_CHAR FUNCTION                                                

1. Output the date as DD of Month, like "1st of January" and "28th of September".  In this case, I am able to output day and month in the desired format, but not with the word 'or' in the middle'.

SELECT to_char(DT_GREGORIAN, 'DD "of" Month')   

FROM Table    

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING DD "of" Month IS NOT VALID

         FOR THE TO_CHAR FUNCTION                                         

This SQL works

SELECT to_char(DT_GREGORIAN,'DD Month')

FROM Table            

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                     

---------+---------+---------+---------+------

13 January                                   

Hello.  I hope you are well.

I have a need to convert a date into string.  I've been able to use the to_char function on some pieces, but am having difficulties with the following two items.  I'm not sure if the capability exists.

1. Output the day of month with the ordinal suffix (st/nd/rd/th).  For example, 2021-02-01 output as 1st and 2021-01-28 is returned as 28th.

SELECT to_char(DT_GREGORIAN, 'FMDDth')   

FROM Table           

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING FMDDth IS NOT VALID FOR THE

TO_CHAR FUNCTION                                                

1. Output the date as DD of Month, like "1st of January" and "28th of September".  In this case, I am able to output day and month in the desired format, but not with the word 'or' in the middle'.

SELECT to_char(DT_GREGORIAN, 'DD "of" Month')   

FROM Table    

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                      

DSNT408I SQLCODE = -20447, ERROR:  FORMAT STRING DD "of" Month IS NOT VALID

         FOR THE TO_CHAR FUNCTION                                         

This SQL works

SELECT to_char(DT_GREGORIAN,'DD Month')

FROM Table            

WHERE DT_GREGORIAN = '2021-01-13'            

  AND CD_CO = 33                             

WITH UR;                                     

---------+---------+---------+---------+------

13 January