DATEFORMAT Function - Sybase SQL Anywhere to SQL Server Migration

In Sybase SQL Anywhere (Sybase ASA), DATEFORMAT function converts a datetime value to a string using the specified format.

In SQL Server, you can use CONVERT or CAST functions to convert a datetime value (DATETIME, DATETIME2 data types i.e.) to a string.

Sybase SQL Anywhere:

  -- Convert the current date to YYYY-MM-DD format
  SELECT DATEFORMAT(GETDATE(), 'YYYY-MM-DD');
  # 2013-08-12

SQL Server:

  -- Convert the current date to YYYY-MM-DD format
  SELECT CONVERT(VARCHAR(10), GETDATE(), 120);
  # 2013-08-12

DATEFORMAT 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)

You can map a Sybase DATEFORMAT format to SQL Server CONVERT style as follows:

Conversion examples:

	Sybase SQL Anywhere	SQL Server
1	DATEFORMAT(GETDATE(), 'YYYY-MM-DD')	CONVERT(VARCHAR(10), GETDATE(), 20)
2	DATEFORMAT(GETDATE(), 'YYYY-MM-DD HH24:MI:SS')	CONVERT(VARCHAR(19), GETDATE(), 20)
3	DATEFORMAT(GETDATE(), 'YYYYMMDD')	CONVERT(VARCHAR(8), GETDATE(), 112)
4	DATEFORMAT(GETDATE(), 'YYYYMM')	CONVERT(VARCHAR(6), GETDATE(), 112)
5	DATEFORMAT(GETDATE(), 'YYMM')	CONVERT(VARCHAR(4), GETDATE(), 12)
6	DATEFORMAT(GETDATE(), 'YYYY/MM/DD')	CONVERT(VARCHAR(10), GETDATE(), 111)

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2013.

You could leave a comment if you were logged in.