DATENAME Function - SQL Server to PostgreSQL Migration

In Microsoft SQL Server (MS SQL) you can use DATENAME function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value as string. In PostgreSQL you can use 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	year	DATENAME(yy, GETDATE())	TO_CHAR(NOW(), 'YYYY')	2022
qq	quarter	DATENAME(qq, GETDATE())	TO_CHAR(NOW(), 'Q')	4
mm	month	DATENAME(mm, GETDATE())	TO_CHAR(NOW(), 'FMMonth')	December
wk	week	DATENAME(wk, GETDATE())	TO_CHAR(NOW(), 'WW')	52
dd	day	DATENAME(dd, GETDATE())	TO_CHAR(NOW(), 'DD')	29
dy	dayofyear	DATENAME(dy, GETDATE())	TO_CHAR(NOW(), 'DDD')	363
dw	weekday	DATENAME(dw, GETDATE())	TO_CHAR(NOW(), 'Day')	Thursday
hh	hour	DATENAME(hh, GETDATE())	TO_CHAR(NOW(), 'HH24')	13
mi	minute	DATENAME(mi, GETDATE())	TO_CHAR(NOW(), 'MI')	31
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.