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