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

Summary information:

Oracle TO_CHAR supports the following format specifiers for datetime values:

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.

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.

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

Other format specifiers that do not have corresponding styles in SQL Server:

Conversion examples:

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

For more information, see Oracle to SQL Server Migration.