DATEPART Function - SQL Server to PostgreSQL Migration

In Microsoft SQL Server (MS SQL) you can use DATEPART function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value as number.

In PostgreSQL you can use DATE_PART function but note that unit can be different and specified as a string literal.

SQL Server:

  -- Get the day
  SELECT DATEPART(day, '2023-07-24');
  # 24

PostgreSQL:

  -- Get the week day
  SELECT DATE_PART('day', '2023-07-24'::date);
  # 24

Mapping SQL Server DATEPART Units to PostgreSQL

You can map SQL Server DATEPART units to the appropriate DATE_PART units in PostgreSQL as follows:

SQL Server			PostgreSQL	Output Example
yy	year	DATEPART(yy, GETDATE())	DATE_PART('year', NOW())	2024
qq	quarter	DATEPART(qq, GETDATE())	DATE_PART('quarter', NOW())	3
mm	month	DATEPART(mm, GETDATE())	DATE_PART('month, NOW())	7
wk	week	DATEPART(wk, GETDATE())	DATE_PART('week', NOW())	30
dd	day	DATEPART(dd, GETDATE())	DATE_PART(NOW(), 'DD')	29
dy	dayofyear	DATEPART(dy, GETDATE())	DATE_PART('doy', NOW())	205
dw	weekday	DATEPART(dw, GETDATE())	DATE_PART('dow', NOW())	1
hh	hour	DATEPART(hh, GETDATE())	DATE_PART('hour', NOW())	13
mi	minute	DATEPART(mi, GETDATE())	DATE_PART('minute', NOW())	31
ss	second	DATEPART(ss, GETDATE())	DATE_PART('second', NOW())	11
ms	millisecond	DATEPART(ms, GETDATE())	DATE_PART('milliseconds', NOW())	777

For more information, see SQL Server to PostgreSQL Migration.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

DATEPART Function - SQL Server to PostgreSQL Migration

Mapping SQL Server DATEPART Units to PostgreSQL