DATETRUNC Function - SQL Server to PostgreSQL Migration

In Microsoft SQL Server, the DATETRUNC function (available since SQL Server 2022) truncates a datetime value to the specified unit.

In PostgreSQL, you can use the DATE_TRUNC function but note that the corresponding unit can be different and specified as a string literal.

SQL Server:

  -- Truncate to the start of month
  SELECT DATETRUNC(month, '2025-04-05');
  /* 2025-04-01 00:00:00.0000000 */

PostgreSQL:

  -- Truncate to the start of month
  SELECT DATE_TRUNC('month', '2025-04-05'::TIMESTAMP);
  /* 2025-04-01 00:00:00 */

Mapping SQL Server DATETRUNC Units to PostgreSQL

You can map SQL Server DATETRUNC units to the corresponding DATE_TRUNC units in PostgreSQL as follows: