What's the equivalent of Oracle's to_char in AS400 DB2 SQL syntax?

oracle db2 ibm-midrange to-char
Nick Pierpoint picture Nick Pierpoint · Jul 14, 2009 · Viewed 10.1k times · Source

I'm trying to display a DATE field fetched from a DB2 instance.

In Oracle I'd use something like:

to_char(v_date, 'YYYY-MM-DD')

What's the equivalent in AS400 DB2?

Answer

Tracy Probst picture Tracy Probst · Jul 15, 2009

In V5R3 or later, use the CHAR() function. To get the same results as your Oracle example, use this:

char(v_date, ISO)

When using the CHAR() function with date fields, you can choose from the following formats: ISO, USA, EUR, JIS, and local. When using "local" as the format, it will use the attributes of the ODBC connection job, which will probably be the system-level values of date format and date separator. The other date formats are as such:

ISO = 'yyyy-mm-dd'
USA = 'mm/dd/yyyy'
EUR = 'dd.mm.yyyy'
JIS = 'yyyy-mm-dd'

In V5R4, you can use the varchar_format function. The only valid formats for this function are 'YYYY-MM-DD HH24:MI:SS' and 'YYYY-MM-DD'.

In V6R1 you have better formatting options for the varchar_format function. As mentioned in another answer, to_char is an alternative to varchar_format.

Related questions

Difference between CLOB and BLOB from DB2 and Oracle Perspective?

I have been pretty much fascinated by these two data types. According to Oracle Docs, they are presented as follows : BLOB : Variable-length binary large object string that can be up to 2GB (2,147,483,647) long. Primarily intended to hold non-traditional data, such …

Read More
decimal(s,p) or number(s,p)?

recently, while working on a db2 -> oracle migration project, we came across this situation. the developers were inadvertently creating new table structures using decimal(s,p) columns. I didn't remember Oracle supporting this, but then some digging showed …

Read More
DB2 database in Oracle SQL developer

I've heard it's possible to connect to a mainframe DB2 database with a client like Oracle SQL developer. I've looked on-line and can't seem to find the connector files needed to do this in SQL developer. Can anyone direct me …

Read More