DATEFORMAT function converts a datetime expression to string using the specified format.
Quick Example:
SELECT DATEFORMAT('2010-09-17', 'Mmm DD, YYYY'); -- Result: Sep 17, 2010
For more information, see DATEFORMAT Function in Sybase SQL Anywhere (Sybase ASA).
Conversion summary:
Sybase SQL Anywhere | Oracle | ||
Syntax | DATEFORMAT(datetime, format) | TO_CHAR(datetime, format) | Differences in format specifiers |
Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g
Format specifiers in Sybase SQL Anywhere and Oracle are different:
Most typical examples of DATEFORMAT conversion from Sybase SQL Anywhere to Oracle:
Converting date to the US format (month, day and year).
You have to change DATEFORMAT to TO_CHAR, no changes in the format required:
Sybase SQL Anywhere:
-- Current date in this example is March 30, 2012 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'MM-DD-YYYY'); -- Result: 03-30-2012
Oracle:
-- Current date in this example is March 30, 2012 SELECT TO_CHAR(SYSTIMESTAMP, 'MM-DD-YYYY') FROM dual; -- Result: 03-30-2012
Converting datetime to the ISO format (year, month, day, hour, minute and second).
You have to change DATEFORMAT to TO_CHAR, and map the time format specifiers:
Sybase SQL Anywhere:
-- Current date and time in this example is March 30, 2012 13:31:07.123456 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD HH:NN:SS.SSS'); -- or using :MM instead of :NN SELECT DATEFORMAT(CURRENT TIMESTAMP, 'YYYY-MM-DD HH:MM:SS.SSS'); -- Result: 2012-03-30 13:31:07.123
Note that :MM (colon before MM) specifies minutes while -MM specifies month in Sybase ASA.
Oracle:
-- Current date and time in this example is March 30, 2012 13:31:07.123456 SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual; -- Result: 2012-03-30 13:31:07.123
Converting date to a form with abbreviated month (01-JAN-2012 i.e.).
You have to change DATEFORMAT to TO_CHAR, and map the month format specifiers:
Sybase SQL Anywhere:
-- Current date and time in this example is March 30, 2012 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'DD-MMM-YYYY'); -- Result: 30-MAR-2012 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'DD-Mmm-YYYY'); -- Result: 30-Mar-2012 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'DD-mmm-YYYY'); -- Result: 30-mar-2012
Note that specifying MMM, Mmm or mmm you can change the case of the month in Sybase ASA.
Oracle:
-- Current date and time in this example is March 30, 2012 SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY') FROM dual; -- Result: 30-MAR-2012 SELECT TO_CHAR(SYSTIMESTAMP, 'DD-Mon-YYYY') FROM dual; -- Result: 30-Mar-2012 SELECT TO_CHAR(SYSTIMESTAMP, 'DD-mon-YYYY') FROM dual; -- Result: 30-mar-2012
Also specifying MON, Mon or mon you can change the case of the month in Oracle.
Converting a time value to various formats.
You have to change DATEFORMAT to TO_CHAR, and map the time format specifiers:
Sybase SQL Anywhere:
-- Current date and time in this example is March 30, 2012 13:31:07.123456 SELECT DATEFORMAT(CURRENT TIMESTAMP, 'HH:NN'); -- Result: 13:31 -- Convert to 12-hour format with AM/PM SELECT DATEFORMAT(CURRENT TIMESTAMP, 'HH:NN AA'); -- Result: 01:31 PM
Note that HH specifies 24-hour format if it is used without AA or PP, otherwise it specifies 12-hour format in Sybase ASA.
Oracle:
-- Current date and time in this example is March 30, 2012 13:31:07.123456 SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI') FROM dual; -- Result: 13:31 -- Convert to 12-hour format without AM/PM SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI') FROM dual; -- Result: 01:31 -- Convert to 12-hour format with AM/PM SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI AM') FROM dual; -- Result: 01:31 PM
Note that HH specifies 12-hour format and does not output AM/PM if AM or PM is not specified in the format string in Oracle.