Sybase SQL Anywhere - DATEFORMAT Function - Convert Datetime to String
ybase SQL Anywhere DATEFORMAT function:
Syntax | DATEFORMAT(datetime_expression, format_string) |
Return Value | VARCHAR |
Last Update: Sybase SQL Anywhere 12
Format Specifiers
Sybase SQL Anywhere DATEFORMAT function supports the following format specifiers:
YYYY | 4-digit year | ||||
YY | 2-digit year | ||||
MM | 2-digit month (01-12) | Mm to suppress leading zero | |||
Mmm | Character form for month | MMM for JAN | Mmm for Jan | Mmmmmmm for January | |
DD | 2-digit day of month | Dd to suppress leading zero | |||
Ddd | Character form for day | DDD for FRI | Ddd for Fri | Dddddd for Friday | |
HH | Hour (0 - 23) | ||||
NN | Minutes (0 - 59) | :MM also can be used for minutes (colon before MM) | |||
SS.SSSSSS | Seconds and fraction of a second up to 6 digits (microsecond) | ||||
AA or PP | A.M. or P.M. (12 hour clock) |
Details
DATEFORMAT functions allows you converting a datetime value to the desired string format.
Year, Month and Day
You can get various string representations for dates:
-- Convert September 17, 2010 to '09-17-10' (2-digit month, 2-digit day and 2 digit year)
SELECT DATEFORMAT('2010-09-17', 'MM-DD-YY');
-- Convert to '9/17/10' (month without leading 0)
SELECT DATEFORMAT('2010-09-17', 'Mm/DD/YY');
-- Convert to '17-SEP-2010' (3 letters for month)
SELECT DATEFORMAT('2010-09-17', 'DD-MMM-YYYY');
-- Convert to 'September 17, 2010' (full month name)
SELECT DATEFORMAT('2010-09-17', 'Mmmmmmmmm DD, YYYY');
Hour, Minute, Second and Fraction of Second
You can also get various string representations for time:
-- Convert '17:31:07.123456' to '17:31' (hours and minutes only)
SELECT DATEFORMAT('17:31:07.123456', 'HH:NN');
-- Convert to '05:31 PM' (12-hour format)
SELECT DATEFORMAT('17:31:07.123456', 'HH:NN AA');
SELECT DATEFORMAT('17:31:07.123456', 'HH:MM AA'); -- :MI is minutes (colon required) while MM is month
-- Convert to '07.123' (seconds and 3 digit in fraction - milliseconds)
SELECT DATEFORMAT('17:31:07.123456', 'SS.SSS');