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

(BTW - if you're looking for a function, see TIMESTAMP_ISO())

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

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

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