Skip to main content

FORMAT

The FORMAT function in SQL is used to format date, time, and timestamp values into a specified format.

Syntax​

FORMAT(value, format)
  • value: The date, time, or timestamp value that is to be formatted.
  • format: The format in which the value is to be displayed. This is a string that contains format specifiers, such as %Y for four-digit year, %m for two-digit month, and so on.

Usage​

Here are examples of how FORMAT can be used to display datetime components in various formats:

  1. Formatting a DATE value:

    SELECT FORMAT(DATE '2017-06-15','%Y-%m') AS date;

    This returns "2017-06".

  2. Formatting a TIMESTAMP value:

    SELECT FORMAT(TIMESTAMP '2015-09-05 23:56:04', '%Y-%m-%d %H:%M:%S') AS timestamp;

    This returns "2015-09-05 23:56:04".

  3. Formatting a TIME value:

    SELECT FORMAT(TIME '23:56:04','%H:%M') AS time;

    This returns "23:56".

  4. Formatting different components of a TIMESTAMP value separately:

    SELECT 
        FORMAT(TIMESTAMP '2015-09-05 23:56:04', '%Y') AS year,
        FORMAT(TIMESTAMP '2015-09-05 23:56:04', '%m') AS month,
        FORMAT(TIMESTAMP '2015-09-05 23:56:04', '%d') AS day;

    This returns:

    year | month | day
    -----+-------+-----
    2015 |    09 |  05

Please note that the FORMAT function only accepts date, time, or timestamp values. If you try to format a value with an incorrect type, you will encounter an error.

Error Example​

SELECT FORMAT('2015-09-05 23:56:04', '%Y-%m-%d %H') AS timestamp;

This will throw an error because the input value is a string, not a date, time, or timestamp value:

EvaluateError::UnsupportedExprForFormatFunction("2015-09-05 23:56:04".to_owned())