DATEFORMAT function [Date and time]

Function

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

Syntax

DATEFORMATdatetime-expression, string-expression )

Parameters

datetime-expression The date/time to be converted. Must be a date, time, timestamp, or character string.

string-expression The format of the converted date.

Example

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

Usage

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;

Standards and compatibility

See also

DATE_FORMAT option in Reference: Statements and Options