DATENAME Function - SQL Server to PostgreSQL Migration

In Microsoft SQL Server (MS SQL), the DATENAME function extracts the specified unit (a date part such as year, month, day etc.) from a datetime value as string.

In PostgreSQL, you can use the TO_CHAR function with the specific format type.

SQL Server:

  -- Get the name of week day
  SELECT DATENAME(dw, '2022-12-29')
  # Thursday

PostgreSQL:

  -- Get the name of week day
  SELECT TO_CHAR(DATE '2022-12-29', 'Day');
  # Thursday

You can map SQL Server DATENAME units to the appropriate TO_CHAR format in PostgreSQL as follows:

SQL Server			PostgreSQL	Output Example
yy	yyyy	year	DATENAME(yy, GETDATE())	TO_CHAR(NOW(), 'YYYY')	2022
q	qq	quarter	DATENAME(qq, GETDATE())	TO_CHAR(NOW(), 'Q')	4
m	mm	month	DATENAME(mm, GETDATE())	TO_CHAR(NOW(), 'FMMonth')	December
ww	wk	week	DATENAME(wk, GETDATE())	TO_CHAR(NOW(), 'WW')	52
isowk	isoww	iso_week	DATENAME(isowk, GETDATE())	TO_CHAR(NOW(), 'IW')	52
d	dd	day	DATENAME(dd, GETDATE())	TO_CHAR(NOW(), 'DD')	29
y	dy	dayofyear	DATENAME(dy, GETDATE())	TO_CHAR(NOW(), 'DDD')	363
w	dw	weekday	DATENAME(dw, GETDATE())	TO_CHAR(NOW(), 'Day')	Thursday
hh	hour		DATENAME(hh, GETDATE())	TO_CHAR(NOW(), 'HH24')	13
n	mi	minute	DATENAME(mi, GETDATE())	TO_CHAR(NOW(), 'MI')	31
s	ss	second	DATENAME(ss, GETDATE())	TO_CHAR(NOW(), 'SS')	11
ms	millisecond		DATENAME(ms, GETDATE())	TO_CHAR(NOW(), 'FF3')	777

In SQL Server, DATENAME(month, exp) returns the full month name capitalized:

SQL Server:

  SELECT DATENAME(month, '2025-05-21');
  /* May */
 
  -- No padding for DATENAME output
  SELECT '<' + DATENAME(month, '2025-05-21') + '>';
  /* <May> */

In PostgreSQL, TO_CHAR(exp, 'Month') function pads the month name with trailing spaces to 9 characters (September (9 letters) the longest month name in English).

You can use TO_CHAR(exp, 'FMMonth') to avoid any padding.

PostgreSQL:

  SELECT TO_CHAR(DATE '2025-05-21', 'FMMonth');
  /* May */
 
  -- No padding for 'FMMonth'
  SELECT '<' || TO_CHAR(DATE '2025-05-21', 'FMMonth') || '>';
  /* <May> */
 
  -- Padding for 'Month'
  SELECT '<' || TO_CHAR(DATE '2025-05-21', 'Month') || '>';
  /* <May      > */
 
  -- Define the output case
  SELECT 
    TO_CHAR(DATE '2025-05-21', 'FMMonth') AS capitalized,
    TO_CHAR(DATE '2025-05-21', 'FMmonth') AS lowercase,
    TO_CHAR(DATE '2025-05-21', 'FMMONTH') AS uppercase;
    /* May           may        MAY */

For more information, see SQL Server to PostgreSQL Migration.