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


  -- 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.