TO_CHAR - Convert Datetime to String - Oracle to SQL Server Migration

In Oracle, TO_CHAR function converts a datetime value (DATE, TIMESTAMP data types i.e.) 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.

Oracle:

  -- Convert the current date to YYYY-MM-DD format
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
  # 2012-07-19

SQL Server:

  -- Convert the current date to YYYY-MM-DD format
  SELECT CONVERT(VARCHAR(10), GETDATE(), 120);
  # 2012-07-19

TO_CHAR for Datetime Conversion Overview

Summary information:

Oracle SQL Server
Syntax TO_CHAR(datetime, format) CONVERT(VARCHAR(n), datetime, style)
CAST(datetime as VARCHAR(n))
Default Format and Style Specified by NLS_DATE_FORMAT Mon DD YYYY HH12:MI

TO_CHAR Format Specifiers

Oracle TO_CHAR supports the following format specifiers for datetime values:

Oracle TO_CHAR Format Specifier
YYYY 4-digit year
YY 2-digit year
MON Abbreviated month (Jan - Dec)
MONTH Month name (January - December)
MM Month (1 - 12)
DY Abbreviated day (Sun - Sat)
DD Day (1 - 31)
D Day of week (1-7)
HH24 Hour (0 - 23)
HH or HH12 Hour (1 - 12)
MI Minutes (0 - 59)
SS Seconds (0 - 59)

Converting Oracle TO_CHAR to SQL Server

Unlike Oracle TO_CHAR function that allows you to build any format string using format specifiers (YYYY and MM i.e.), in SQL Server, you have to use a datetime style that defines the format for the entire datetime string.

Default Format

In Oracle, the default format of a datetime string depends on the NLS_DATE_FORMAT session variable:

Oracle:

  -- Convert to string with the default format
  SELECT TO_CHAR(SYSDATE) FROM dual;
  # 20-JUL-12
 
  -- Change the default format
  ALTER SESSION SET NLS_DATE_FORMAT = 'Mon DD, YYYY';
 
  -- Convert to string with the default format
  SELECT TO_CHAR(SYSDATE) FROM dual;
  # Jul 20, 2012

In SQL Server, the default format is Mon DD YYYY HH12:MI:

SQL Server:

  -- Convert to sting with the default format
  SELECT CAST(GETDATE() AS VARCHAR);
  # Jul 20 2012  1:04PM
 
  -- Convert to sting with the default format
  SELECT CONVERT(VARCHAR, GETDATE());
  # Jul 20 2012  1:04PM

Note that SQL Server CAST can convert with the default format only. The only thing you can do is to right-trim the string by specifying the size of VARCHAR:

SQL Server:

  -- Get Mon DD YYYY format using CAST
  SELECT CAST(GETDATE() AS VARCHAR(11));
  # Jul 20 2012

Using CONVERT function in SQL Server, you can specify different output formats.

Mapping Oracle TO_CHAR Formats to SQL Server CONVERT Style

You can map an Oracle TO_CHAR format to SQL Server CONVERT style as follows:

Oracle TO_CHAR 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
6 YYYY VARCHAR(4) 112
7 YYYY/MM/DD VARCHAR(10) 111
8 DD/MM/YYYY VARCHAR(10) 103
9 MM/DD/YYYY VARCHAR(10) 101
10 DD MON YYYY VARCHAR(11) 106
11 HH24:MI VARCHAR(5) 8, 108, 14 and 114
12 HH24:MI:SS VARCHAR(8) 8, 108, 14 and 114

Conversion examples:

Oracle SQL Server
1 TO_CHAR(SYSDATE, 'YYYY-MM-DD') CONVERT(VARCHAR(10), GETDATE(), 20)
2 TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') CONVERT(VARCHAR(19), GETDATE(), 20)
3 TO_CHAR(SYSDATE, 'YYYYMMDD') CONVERT(VARCHAR(8), GETDATE(), 112)
4 TO_CHAR(SYSDATE, 'YYYYMM') CONVERT(VARCHAR(6), GETDATE(), 112)
5 TO_CHAR(SYSDATE, 'YYMM') CONVERT(VARCHAR(4), GETDATE(), 12)
6 TO_CHAR(SYSDATE, 'YYYY') CONVERT(VARCHAR(4), GETDATE(), 112)
CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
CONVERT(VARCHAR, YEAR(GETDATE()))
7 TO_CHAR(SYSDATE, 'YYYY/MM/DD') CONVERT(VARCHAR(10), GETDATE(), 111)
8 TO_CHAR(SYSDATE, 'DD/MM/YYYY') CONVERT(VARCHAR(10), GETDATE(), 103)
9 TO_CHAR(SYSDATE, 'MM/DD/YYYY') CONVERT(VARCHAR(10), GETDATE(), 101)
10 TO_CHAR(SYSDATE, 'DD MON YYYY') CONVERT(VARCHAR(11), GETDATE(), 106)
11 TO_CHAR(SYSDATE, 'D') CONVERT(VARCHAR, DATEPART(DW, GETDATE()))
12 TO_CHAR(SYSDATE, 'HH24:MI') CONVERT(VARCHAR(5), GETDATE(), 8)
13 TO_CHAR(SYSDATE, 'HH24:MI:SS') CONVERT(VARCHAR(8), GETDATE(), 8)

You can also use expressions to get the formats that differs from available styles:

Oracle SQL Server
1 TO_CHAR(exp, 'MON-YYYY') REPLACE(RIGHT(CONVERT(VARCHAR(11), exp, 106), 8), ' ', '-')
2 TO_CHAR(exp, 'YYYY/MM/DD HH24:MI:SS') REPLACE(CONVERT(VARCHAR(19), exp, 20), '-', '/')

For more information, see Oracle to SQL Server Migration.

You could leave a comment if you were logged in.