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)

Mapping Sybase DATEFORMAT Formats to SQL Server CONVERT Style

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

Sybase DATEFORMAT Format SQL Server CONVERT Data Type SQL Server CONVERT Style
1 YYYY-MM-DD VARCHAR(10) 20, 21, 120, 121, 126 and 127
2 YYYY-MM-DD HH24:MI:SS VARCHAR(19) 20, 21, 120 and 121
3 YYYYMMDD VARCHAR(8) 112
4 YYYYMM VARCHAR(6) 112
5 YYMM VARCHAR(4) 12

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)

Database and SQL Migration Tools

About SQLines

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.