FORMAT_DATE

The FORMAT_DATE function converts a Date into a String with two optional parameters, a configurable format, and a locale.

The FORMAT_DATE function interprets its parameters the same as the FORMAT_DATETIME does, see that article for more details.

Declaration

FORMAT_DATE(date, format_string, locale) -> formatted_date_string

Parameters

date (type: date)

Any date

format_string (optional, type: string, default: ISO 8601 Format, "YYYY-MM-DDThh:mm:ssZ")

The string representing the format to be outputted. The Date units available in the format can be found in Data and Time Formatting Options.

locale (optional, type: string, default: The user's current locale)

The locale the date should be formatted in. This will configure which language formats containing month, and weekday names. Locale must match a valid LCID String.

Return Values

formatted_date_string (type: string)

The formatted date.

Examples

For the following examples assume we have a variable named example_date that holds a Date that would format to the string "January 9th, 2007".

FORMAT_DATE(  
  example_date,   
  "MMMM Do, YYYY"
) -> "January 9th, 2007"

As with FORMAT_DATETIME, when called with no format string, FORMAT_DATE will generate an ISO 8601 and RFC3339 compatible timestamp. The time portion of the timestamp will be filled with zeros.

FORMAT_DATE(example_date) -> "2007-01-09T00:00:00Z"

A date in the U.S. is commonly presented with numbers for the month, day, and year separated by slashes.

FORMAT_DATE(example_date, "MM/DD/YYYY") -> "01/9/2007"

Be careful when formating a date using numbers for months as the order changes depending on the user's locale. In some locales the day comes first.

FORMAT_DATE(example_date, "DD/MM/YYYY") -> "09/01/2007"

Consider that a string like "01/09/2007" could be interpreted as January 9th or September 1st. This is problematic because a format string will not be able to take into account the user's locale. Prefer to put the full four-digit year first, then the month, then the day when clarity is important.

FORMAT_DATE(example_date, "YYYY/DD/MM") -> "2007/01/09"

Discussion

The DATE_FROM_FORMAT function is the opposite of FORMAT_DATE. Rather than converting a Date to a String, it converts a String into a Date by parsing with a given format string.