Db2

 View Only
Expand all | Collapse all

How to convert MM/DD/YYYY to YYYY-MM-DD

  • 1.  How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Mon October 30, 2023 03:51 PM

    I have file of length 852 bytes and this file have multiple date position in date format MM/DD/YYYY(Most of the date fields have date value but some of them are having spaces as well). I want to convert these valid date value only to YYYY-MM-DD. I have tried many things but none of them seems to be working. Can anyone please help me achieve this ?



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


  • 2.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Mon October 30, 2023 05:28 PM
    Edited by Jan Nelken Mon October 30, 2023 05:46 PM

    C:\>db2 values(current date)

    1
    ----------
    10/30/2023

      1 record(s) selected.


    C:\>db2 values(year(current date) concat '-' concat month(current date) concat '-' concat day(current date))

    1
    -----------------------------------
    2023-10-30

      1 record(s) selected.



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 3.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Tue October 31, 2023 01:53 AM

    Thank you for your response, Jan.

    The issue is that it's not the current date in the file. The file having multiple date field (none of them are current date). Is there any way that I can covert these dates through JCL sort?



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

    Original Message


  • 4.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Tue October 31, 2023 03:28 AM
    Edited by Jan Nelken Tue October 31, 2023 04:05 AM

    CURRENT DATE (Db2 special register) I used in for a quick example giving you an idea.
    If string MyDate contains '10/31/2023' then replacing "Current date" with MyDate in second line gives you desired output.



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 5.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Thu December 14, 2023 11:31 AM

    If you want to use sort, I asume dfsort, in a JCL step you would probably need to write some sort of exit routine for dfsort. This is outside of my area of expertise but it should be described in the dfsort application programing guide.

    Using SQL, the following should do the trick,

    date(timestamp_iso('10/30/2023'))

     1
     ----------
     2023-10-30



    ------------------------------
    Sven Heidorn
    ------------------------------

    Original Message


  • 6.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Thu December 14, 2023 11:49 AM
    Edited by Jan Nelken Thu December 14, 2023 11:56 AM

    Are you sure?


    Yet another way:



    ------------------------------
    Jan Nelken
    ------------------------------

    Original Message


  • 7.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Fri December 15, 2023 04:16 AM

    Well, obviously no if you put it that way :-)

    $ db2 "values date(timestamp_iso('10/30/2023'))"

    1
    ----------
    10/30/2023

      1 record(s) selected.

    $ db2 "values date(timestamp('10/30/2023'))"

    1
    ----------
    10/30/2023

      1 record(s) selected.

    These where run from the command prompt and the following two from Data Studio against the same database

    1> values date(timestamp_iso('10/30/2023'))
    2> go
     1
     ----------
     2023-10-30
    1> values date(timestamp('10/30/2023'))
    2> go
    1>  
    2> go
    1> Failed queries => 1
    The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=4.31.10

    So, it seems, IT Depends :-) 

    But the original question was if this cuold be done using sort in a JCL, so I will revise my previous statement as well. 

    The OUTREC control statement of dfsort may be used to convert the date fields but I won't try to get into how that would be done since its been quite a while since I was working actively on z/Os and as I said, this is not my area of experties.  



    ------------------------------
    Sven Heidorn
    ------------------------------

    Original Message


  • 8.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Fri December 15, 2023 08:49 AM

    Instead of DFSort - why not use the "KISS" method and scan the file with REXX to clean up / convert all the date formats into a single "Known" format.

    https://www.ibm.com/docs/en/cics-ts/5.5?topic=f-built-in-functions





    ------------------------------
    Steve Able
    Director of Strategy and Architecture
    Adaptigent formally GT Software, Inc.
    ☠
    ------------------------------

    Original Message


  • 9.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Mon December 18, 2023 01:24 AM

    Once you have located the date, you may try something like this:

    Case When YourDate > '' Then VarChar_Format(Timestamp_Format(YourDate, 'MM/DD/YYYY'), 'YYYY-MM-DD')
           Else ''
           End



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

    Original Message


  • 10.  RE: How to convert MM/DD/YYYY to YYYY-MM-DD

    Posted Mon December 18, 2023 01:56 AM

    Certainly! To achieve this in a DB2 context, you can use the CASE statement along with the DATE and VARCHAR_FORMAT functions. Assuming that your date values are in a consistent position within the file, you can create a SQL query to extract and format the valid date values.

    Let's assume your table is named your_table and the column containing the date is named date_column. Here's an example query:

    SELECT 
        CASE 
            WHEN LENGTH(TRIM(date_column)) = 10 AND POSITION('/', TRIM(date_column)) = 3 AND POSITION('/', TRIM(date_column), 4) = 6
            THEN VARCHAR_FORMAT(DATE(SUBSTR(TRIM(date_column), 7, 4) || '-' || SUBSTR(TRIM(date_column), 1, 2) || '-' || SUBSTR(TRIM(date_column), 4, 2)), 'YYYY-MM-DD')
            ELSE NULL
        END AS formatted_date
    FROM your_table;

    This query checks if the length of the trimmed date is 10 characters, and if the '/' character is at positions 3 and 6. If these conditions are met, it assumes it's a valid date in MM/DD/YYYY format and then converts it to the desired format (YYYY-MM-DD).

    Make sure to replace your_table and date_column with your actual table and column names. Also, note that this assumes the date format is always MM/DD/YYYY. If there are variations, you might need to adjust the conditions accordingly



    ------------------------------
    Sarwar Javaid
    Sr. Systems Engineer | Information Technology
    Dollar Industries Pvt. Ltd. | D-85, S.I.T.E Area, Karachi
    Cell: (+92) 346 2595970
    ------------------------------

    Original Message