v.23.2Improvement

Add DATE_FORMAT() alias for formatDateTime() to enhance MySQL compatibility

Official CH changelog text
Add an alias "DATE_FORMAT()" for function "formatDateTime()" to improve compatibility with MySQL's SQL dialect, extend function formatDateTime with substitutions "a", "b", "c", "h", "i", "k", "l" "r", "s", "W". ### Documentation entry for user-facing changes User-readable short description: DATE_FORMAT is an alias of formatDateTime. Formats a Time according to the given Format string. Format is a constant expression, so you cannot have multiple formats for a single result column. (Provide link to formatDateTime). #46302 (Jake Bamrah).
DATE_FORMAT() is introduced as an alias for the existing formatDateTime() function, allowing users to format date and time values with a syntax compatible with MySQL. The function formats a Time according to a specified format string and now supports additional substitution parameters: "a", "b", "c", "h", "i", "k", "l", "r", "s", and "W".

Why it matters

This feature improves compatibility with MySQL's SQL dialect by providing an alias for formatDateTime() named DATE_FORMAT(). It enhances usability for users migrating queries from MySQL or working across different SQL dialects by supporting familiar function naming and extending the formatting options with additional substitutions.

How to use it

Use the DATE_FORMAT() function in the same way as formatDateTime() by providing a date/time value and a constant format string. For example:

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s')


The format string must be a constant expression, so different formats require separate expressions or queries.

Related resources

PreviousNext