Db2

 View Only
  • 1.  Convert USA date format to ISO while using unload utility

    Posted Mon November 20, 2023 11:51 AM

    We have multiple date fields in database in USA date format(MM/DD/CCYY) and we want to
    convert those date in ISO format(CCYY-MM-DD) while using Unload utility(DSNUTILB). We are using syntax
    CHAR(DATE_FIELD,ISO) to convert those date field, but it giving some syntax error(Invalid Keyword). We tried the same syntax in QMF to select dates column and Its giving correct result.
    Could anyone please let me know why it is giving error while unload and working fine for simple select statement ? what will the correct syntax to change date format to ISO while unloading?



    ------------------------------
    Prashant Kumar
    ------------------------------


  • 2.  RE: Convert USA date format to ISO while using unload utility

    IBM Champion
    Posted Tue November 21, 2023 01:10 AM

    A Date is a numeric value (scaliger no). Number of days since January, 1st 4713 BC.

    The date format is only used to make the scaliger no readable. The date format used depends on the date format in the job or connection.

    Date Formats in DDS PFs are only used in old tools such as WRKF to make the scaliger no readable.

    ... so, if the date format in the connection is ISO IMHO nothing has to be done. 



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------



  • 3.  RE: Convert USA date format to ISO while using unload utility

    Posted Tue November 21, 2023 02:09 AM

    Thank you for your response, Birgitta. 

    So, what I understood from your response that it's not quite possible to change date from USA to ISO format while unloading. Am I correct?

    Is there any other way to change those dates to ISO format other than DFSORT utility?



    ------------------------------
    Prashant Kumar
    ------------------------------



  • 4.  RE: Convert USA date format to ISO while using unload utility

    IBM Champion
    Posted Wed November 22, 2023 01:38 AM

    A date has NO FORMAT at all it is running number.

    The format you specified in your connection is used to make the date readable. If you change the date format in your connection or job you'll see the date in a different format.



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------