DATEFORMAT function [Date and time]

Returns a string representing a date expression in the specified format.

DATEFORMAT ( datetime-expression, string-expression )

The following statement returns string values like “Jan 01, 1989”:

SELECT DATEFORMAT( start_date, 'Mmm dd, yyyy' ) from Employees;

The following statement returns the string “Feb 19, 1987”:

SELECT DATEFORMAT( CAST ( ‘1987/02/19’ AS DATE ), ‘Mmm Dd, yyyy’ ) FROM iq_dummy

The datetime-expression to convert must be a date, time, or timestamp data type, but can also be a CHAR or VARCHAR character string. If the date is a character string, Sybase IQ implicitly converts the character string to date, time, or timestamp data type, so an explicit cast, as in the example above, is unnecessary.

Any allowable date format can be used for string-expression. Date format strings cannot contain any multibyte characters. Only single-byte characters are allowed in a date/time/datetime format string, even when the collation order of the database is a multibyte collation order like 932JPN.

If '?' represents a multibyte character, then the following query fails:

SELECT DATEFORMAT ( start_date, ‘yy?’) FROM Employees;

Instead, move the multibyte character outside of the date format string using the concatenation operator:

SELECT DATEFORMAT (start_date, ‘yy’) + ‘?’ FROM Employees;