DATENAME Function - Sybase ASE to PostgreSQL Migration

In SAP Sybase Adaptive Server Enterprise (ASE) you can use DATENAME function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value. In PostgreSQL you can use TO_CHAR function with the specific format type.

Sybase ASE:

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

Mapping Sybase ASE DATENAME Units to PostgreSQL

You can map Sybase ASE DATENAME units to the appropriate TO_CHAR format in PostgreSQL as follows:

Sybase ASE 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(), 'MM') 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

For more information, see Sybase ASE to PostgreSQL Migration.