DATEFORMAT Function - Sybase SQL Anywhere to Oracle Migration

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:

Sybase SQL Anywhere	Oracle
YYYY	YYYY		4-digit year
YY	YY		2-digit year
MM	MM		2-digit month (01-12)
Mm			2-digit month (1-12) without leading 0
MMM, Mmm	MON, Mon		Abbreviated name of month - JAN, FEB, MAR (Jan, Feb, Mar) etc.
Mmmmmmm	Month		Month name - January, February, March etc.
DD	DD		Day of month (01-31)
Dd			2-digit day (1-31) without leading 0
DDD, Ddd	DY, Dy		Abbreviated name of day - MON, TUE, WED (Mon, Tue, Wed) etc.
DDDDDDDD, Dddddddd	DAY, Day		Name of day - MONDAY (Monday) i.e
HH	HH24		Hour (0-23)
NN, :MM	MI		Minutes (0 - 59)
SS.SSSSSS	SS.FF6		Seconds and fraction of a second
AA or PP	AM or PM		AM or PM (12 hour clock)

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.

Sybase SQL Anywhere

• Reference and Articles
• DATEFORMAT Function

Oracle

• Reference and Articles