DATETIME2 Data Type - SQL Server to PostgreSQL Migration

In SQL Server, DATETIME2(p) data type stores date and time with fractional seconds. In PostgreSQL, you can use TIMESTAMP(p) data type.

SQL Server:

  -- Sample table with DATETIME2 with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR(30),
    created_dt DATETIME2(3)
  );

PostgreSQL:

   -- Sample table with TIMESTAMP with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR(30),
    created_dt TIMESTAMP(3)
  );

Conversion summary:

	SQL Server	PostgreSQL
Syntax	DATETIME2[(p)]	TIMESTAMP[(p)]
Range	0 ⇐ p ⇐ 7	0 ⇐ p ⇐ 6
Date range	0001-01-01 to 9999-12-31	4713 BC to 294276 AD
Accuracy	100 nanoseconds	1 microsecond
Default	p is 7	p is 6

For more information, see SQL Server to PostgreSQL Migration.