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")
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.