DB2 - L

Expand all | Collapse all

TO_CHAR Examples

  • 1.  TO_CHAR Examples

    Posted 10 days ago
    Edited by Bob Kota 10 days ago

    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                                   

     



    ------------------------------
    Bob Kota
    ------------------------------


  • 2.  RE: TO_CHAR Examples

    Posted 10 days ago

    I have a solution for a somewhat similar problem.  You can see where your solution is contained in tis, I think:

     

    SELECT                                       

        CHAR(                                    

             CASE DAYOFWEEK(CURRENT DATE)        

             WHEN 1 THEN 'SUNDAY'                

             WHEN 2 THEN 'MONDAY'                

             WHEN 3 THEN 'TUESDAY'               

             WHEN 4 THEN 'WEDNESDAY'             

             WHEN 5 THEN 'THURSDAY'              

             WHEN 6 THEN 'FRIDAY'                

             WHEN 7 THEN 'SATURDAY'              

             END                                 

             CONCAT ', '                         

             CONCAT                              

             CASE MONTH(CURRENT DATE)            

             WHEN 1  THEN 'JANUARY'              

             WHEN 2  THEN 'FEBRUARY'             

             WHEN 3  THEN 'MARCH'                

             WHEN 4  THEN 'APRIL'                

             WHEN 5  THEN 'MAY'                  

             WHEN 6  THEN 'JUNE'                 

             WHEN 7  THEN 'JULY'                  

             WHEN 8  THEN 'AUGUST'               

             WHEN 9  THEN 'SEPTEMBER'            

             WHEN 10 THEN 'OCTOBER'              

             WHEN 11 THEN 'NOVEMBER'             

             WHEN 12 THEN 'DECEMBER'             

             END                                  

             CONCAT ' '                          

             CONCAT RTRIM(CHAR(DAY(CURRENT DATE)))

             CONCAT                              

             CASE DAY(CURRENT DATE)              

             WHEN 1  THEN 'ST'                    

             WHEN 2  THEN 'ND'                   

             WHEN 3  THEN 'RD'                   

             WHEN 21 THEN 'ST'                   

             WHEN 22 THEN 'ND'                   

             WHEN 23 THEN 'RD'                   

             WHEN 31 THEN 'ST'                   

             ELSE         'TH'                   

             END                                 

             CONCAT ', '                          

             CONCAT RTRIM(CHAR(YEAR(CURRENT DATE)))

             ,31                                   

            )                                     

        AS VERBOSE_DATE                           

        FROM SYSIBM.SYSDUMMY1                     

    ;                                             

     

     

    Philip Sevetson

    Computer Systems Manager

    FISA-OPA

    5 Manhattan West

    New York, NY 10001

    psevetson@fisa-opa.nyc.gov

    917-991-7052 m

    212-857-1659 f

    image001.png@01D261E4.BE68E970

     






  • 3.  RE: TO_CHAR Examples

    Posted 9 days ago
    Bob,

    Sorry formatting of SQL is not best after paste in here.

    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
    etc.

    I could have used Case or Decode, but I think  they perform a bit weakly.

    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------



  • 4.  RE: TO_CHAR Examples

    Posted 9 days ago
    Nice solution!

    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 5.  RE: TO_CHAR Examples

    Posted 9 days ago

    Easy peasy (Sorry a UK term) for a mathemetician and SQL Nutter.

    Some of us use SQL as a report writer, and only REXX if we really have to.
    SQL can do virtually all forms of data manipulation, since V8 saw fancy functions, recursive, etc. and later OLAP was a bonus.
    Only downside is SQL expressions aren't always pretty. Just get the job done fast.



    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------



  • 6.  RE: TO_CHAR Examples

    Posted 9 days ago
    Edited by Michael Hannan 9 days ago

    Bob,

    I see you got pretty close using TO-CHAR function, alternate name for TIMESTAMP_FORMAT. 

    I have not really used that much before if at all.
    I use SQL like a programming language.

    You could have done a simple REPLACE to insert the other bits in place of the intervening blank:

    REPLACE(to_char(DT,'DD Month'), ' '
        ,strip(char(day(dt))) concat
           substr('th st nd rd '
           ,int(substr('1230000000000000000012300000001',day(dt), 1))
           *3+1, 3) concat 'of ')

    I did not test this. Just off top of my head.
    Could be like a fun challenge, to see who can do it in less characters of code.
    There might be an even more concise solution still with good performance.



    ------------------------------
    Michael Hannan
    DB2 SQL and Performance Specialist
    ------------------------------