TO_TIMESTAMP - Convert String to Datetime - PostgreSQL to SQL Server

In PostgreSQL TO_TIMESTAMP function converts a string value to TIMESTAMP data type value using the specified format. In SQL Server you can use CONVERT or TRY_CONVERT function with an appropriate datetime style.

Note that TRY_CONVERT function is available since SQL Server 2012.

PostgreSQL:

  -- Specify a datetime string and its exact format
  SELECT TO_TIMESTAMP('01/13/2023 11:37:13', 'MM/DD/YYYY HH24:MI:SS');

SQL Server:

  -- Specify a datetime string and style 101 (US format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '01/13/2023 11:37:13', 101);
 
  -- TRY_CONVERT returns NULL if conversion fails
  SELECT TRY_CONVERT(DATETIME, '01/13/2023 11:37:13', 101);

PostgreSQL TO_TIMESTAMP supports the following format specifiers (not full list):

Unlike PostgreSQL TO_TIMESTAMP 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.

Fortunately, most applications use typical datetime formats in PostgreSQL that can be easily mapped to a datetime format style in SQL Server.

Note that when converting a string to datetime, both CONVERT and TRY_CONVERT recognize ANSI/ISO datetime formats with various delimiters by default, so you do not need to specify a style for them.

An ANSI/ISO format is year, month, day, hour, minute, seconds, fractional seconds (YYYY-MM-DD HH24:MI:SS.FFF) where trailing parts can be omitted so you can specify YYYY-MM-DD, or YYYY-MM-DD HH24:MI etc.

SQL Server:

  -- ISO date formats with various delimiters recognized by default (year, month, day)
  SELECT CONVERT(DATETIME, '2012-06-30');
  SELECT CONVERT(DATETIME, '2012/06/30');
  SELECT CONVERT(DATETIME, '2012.06.30');
 
  SELECT CONVERT(DATETIME, '2012-06-30 11:10');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012/06/30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012.06.30 11:10:09.333');
 
  -- ISO date without delimiters is also recognized
   SELECT CONVERT(DATETIME, '20120630');

SQL Server also recognizes United States datetime format (month, day, year and time) by default, so you do not need to specify style 101:

SQL Server:

  -- United States date formats with various delimiters recognized by default (month, day, year)
  SELECT CONVERT(DATETIME, '06-30-2012');
  SELECT CONVERT(DATETIME, '06/30/2012');
  SELECT CONVERT(DATETIME, '06.30.2012');
 
  SELECT CONVERT(DATETIME, '06-30-2012 11:10');
  SELECT CONVERT(DATETIME, '06/30/2012 11:10:09');
  SELECT CONVERT(DATETIME, '06.30.2012  11:10:09.333');

Also SQL Server recognizes the following formats by default:

SQL Server

  SELECT CONVERT(DATETIME, '17-FEB-2013');
  # 2013-02-17 00:00:00.000

You can map an PostgreSQL TO_TIMESTAMP format to SQL Server CONVERT or TRY_CONVERT style as follows:

Conversion examples:

For more information, see PostgreSQL to SQL Server Migration