DATENAME Function - Sybase ASE to MariaDB Migration

In Sybase ASE you can use DATENAME function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value. For month and weekday units the function returns a literal (January, Monday etc.), for other units it returns an integer number.

In MariaDB there is no single function with the same functionality, and you have to use YEAR(), MONTH(), DAY() and other functions to extract the required datetime units (date parts).

Sybase ASE:

  -- Get the name of week day
  SELECT DATENAME(dw, '2017-12-17')
  # Sunday

MariaDB:

  -- Get the name of week day
  SELECT DAYNAME('2017-12-17')
  # Sunday

Mapping Sybase ASE DATENAME Units to MariaDB

You can use SQLines SQL Converter to convert Sybase ASE DATENAME function to MariaDB that maps the units to the appropriate datetime functions in MariaDB:

Sybase ASE MariaDB Output Example
yy year DATENAME(yy, GETDATE()) YEAR(NOW()) 2017
qq quarter DATENAME(qq, GETDATE()) QUARTER(NOW()) 4
mm month DATENAME(mm, GETDATE()) MONTHNAME(NOW()) December
wk week DATENAME(wk, GETDATE()) WEEK(NOW()) 51
dd day DATENAME(dd, GETDATE()) DAY(NOW()) 17
dy dayofyear DATENAME(dy, GETDATE()) DAYOFYEAR(NOW()) 351
dw weekday DATENAME(dw, GETDATE()) DAYNAME(NOW()) Sunday
hh hour DATENAME(hh, GETDATE()) HOUR(NOW()) 19
mi minute DATENAME(mi, GETDATE()) MINUTE(NOW()) 22
ss second DATENAME(ss, GETDATE()) SECOND(NOW()) 8
ms millisecond DATENAME(ms, GETDATE()) MICROSECOND(NOW(3))/1000 396
us microsecond DATENAME(us, CURRENT_BIGTIME()) MICROSECOND(NOW(6)) 324091

For more information, see: