Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    58

    Lightbulb Unanswered: Default Date Format

    What is the equivalent parameter of NLS_DATE_FORMAT available in Oracle for DB2? My current date format in one db2 database is 'yyyy-mm-dd' and another is 'yyyy/mm/dd'. How can I keep them in sync?

    Any inputs would be appreciated. Thank you.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by pinecone
    What is the equivalent parameter of NLS_DATE_FORMAT available in Oracle for DB2? My current date format in one db2 database is 'yyyy-mm-dd' and another is 'yyyy/mm/dd'. How can I keep them in sync?

    Any inputs would be appreciated. Thank you.
    What does NLS_DATE_FORMAT do? Need more info.

    Generally in DB2 you can change the date format at BIND time, using the DATETIME option, but it depends on what mechanism you're using to access the database. (ie. CLP/CLI/ODBC/JDBC/etc)

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    (BTW - if you're looking for a function, see TIMESTAMP_ISO())
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Apr 2004
    Posts
    58
    Thanks for the info!

    NLS_DATE_FORMAT is used to format the date so you can modify it to have / or - in between month, day and year. You can also format the order of the those fields.

    By using BIND DATETIME I can only choose between EUR, ISO, JIS, LOC and USA. Since ISO uses YYYY-MM-DD, USA uses MM/DD/YYYY and the rest do not apply, if I need the date format to be MM-DD-YYYY. How can it be achieved?

    Does DB2 allow changing the order of MM DD YYYY globally within the database without using a function to reformat the date? If so, how ?

    Thank you.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by pinecone
    Thanks for the info!

    NLS_DATE_FORMAT is used to format the date so you can modify it to have / or - in between month, day and year. You can also format the order of the those fields.

    By using BIND DATETIME I can only choose between EUR, ISO, JIS, LOC and USA. Since ISO uses YYYY-MM-DD, USA uses MM/DD/YYYY and the rest do not apply, if I need the date format to be MM-DD-YYYY. How can it be achieved?

    Does DB2 allow changing the order of MM DD YYYY globally within the database without using a function to reformat the date? If so, how ?

    Thank you.
    Actually the way the data is stored in DB2 is a non-character format, the formatting is only applied when the data is accessed. So this isn't a data modification exercise as much as it is a formatting issue.

    Presumably you could cobble together anything you wanted using the MONTH, DAY, and YEAR() functions and concat'ing them together. Maybe someone else has a better idea, I've honestly not had to deal with that issue yet.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by pinecone
    Thanks for the info!

    NLS_DATE_FORMAT is used to format the date so you can modify it to have / or - in between month, day and year. You can also format the order of the those fields.

    By using BIND DATETIME I can only choose between EUR, ISO, JIS, LOC and USA. Since ISO uses YYYY-MM-DD, USA uses MM/DD/YYYY and the rest do not apply, if I need the date format to be MM-DD-YYYY. How can it be achieved?

    Does DB2 allow changing the order of MM DD YYYY globally within the database without using a function to reformat the date? If so, how ?

    Thank you.
    Actually the way the date data is stored in DB2 is a non-character format, the formatting is only applied when the data is accessed. So this isn't a data modification exercise as much as it is a formatting issue.

    Presumably you could cobble together anything you wanted using the MONTH, DAY, and YEAR() functions and concat'ing them together. Maybe someone else has a better idea, I've honestly not had to deal with that issue yet.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Aug 2003
    Posts
    106
    Here is an example

    Select AUD_TS
    ,SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),1,4) AS YYYY
    ,SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),6,2) AS MM
    ,SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),9,2) AS DD
    ,SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),1,4) CONCAT '/' CONCAT
    SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),6,2) CONCAT '/' CONCAT
    SUBSTR(VARCHAR_FORMAT(AUD_TS,'YYYY-MM-DD HH24:MIS'),9,2) AS NDATE
    FROM CSS_ORDER

    You are the creator of your own destiny!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •