CONVERT - String to Datetime - SQL Server to PostgreSQL Migration

In SQL Server you can use CONVERT function to convert a string expression in the specified format (style) to a datetime data type (DATE, DATETIME etc.). In PostgreSQL you can to use TO_TIMESTAMP function.

Note that SQL Server CONVERT and PostgreSQL formats are different.

SQL Server:

  -- Convert string to datetime
  SELECT CONVERT(DATETIME, '12/28/2022 11:13:31', 110); 
  # 2022-12-28 11:13:31.000

PostgreSQL:

  -- Convert string to datetime
  SELECT TO_TIMESTAMP('12/28/2022 11:13:31', 'MM/DD/YYYY HH24:MI:SS'); 
  # 2022-12-28 11:13:31+03

Mapping SQL Server Datetime Style to PostgreSQL Format

When you convert CONVERT function to TO_TIMESTAMP you have to map the SQL Server style to the appropriate format string in PostgreSQL:

SQL Server Style PostgreSQL Format String Output Example
101 US 'MM/DD/YYYY' 12/28/2022
112 ISO YYYYMMDD 'YYYYMMDD' '20230119'
110 US 'MM/DD/YYYY HH24:MI:SS' 12/28/2022 11:13:31

Conversion examples:

SQL Server PostgreSQL
CONVERT(DATETIME, '12/28/2022', 101) TO_TIMESTAMP('12/28/2022', 'MM/DD/YYYY')
CONVERT(DATETIME, '20230119', 112) TO_TIMESTAMP('20230119', 'YYYYMMDD')
CONVERT(DATETIME, '12/28/2022 11:13:31', 110) TO_TIMESTAMP('12/28/2022 11:13:31', 'MM/DD/YYYY HH24:MI:SS')

For more information, see SQL Server to PostgreSQL Migration.