Sybase SQL Anywhere - DATEFORMAT Function - Convert Datetime to String

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

Overview

Sybase 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');

Sybase SQL Anywhere DATEFORMAT in Other Databases

DATEFORMAT function in other Databases:

Oracle:

TO_CHAR(datetime_expression, format_string) Differences in format specifiers More...

Sybase SQL Anywhere Resources