FORMAT_DATETIME
Last updated
Was this helpful?
18.5
Last updated
Was this helpful?
The FORMAT_DATETIME function converts a into a with three optional parameters, a configurable format, locale, and timezone.
The format string is constructed using letters to represent different parts of a date. For example, a two-digit year like "07" is represented with two capital "y" characters ("YY"), while a four-digit year is represented with four capital "y" characters ("YYYY"). In general, more elaborate formats use more letters, like the three-letter day of the week abbreviation "ddd" for "Tue", or the full day of the week name "dddd". See the Examples section for common formats. The full list of Date Formatting Options can be found in the collection of .
datetime (required, type: DateTime)
The DateTime to be formatted.
format_string (optional, type: string, default: , "YYYY-MM-DDThh:mm:ssZ")
The string representing the format to be returned. The Date units available in the format can be found in . While including the format_string parameter is optional, it must be included in order for downstream optional parameters (locale and time_zone), should they be included, to be correctly parsed.
locale (optional, type: string, default: The user's current locale)
The locale the datetime should be formatted in. This will configure which language formats containing month, and weekday names. Locale must match a valid . While including the locale parameter is optional, it must be included in order for the downstream optional parameter (time_zone), should it be included, to be correctly parsed.
time_zone (optional, type: *string,*default: TIMEZONE(datetime))
Overrides the timezone the DateTime is formatted in. By default, the timezone of the DateTime is used. Timezones must be described by their , or FORMAT_DATETIME will not parse them as meaningful timezones.
formatted_datetime_string (type: string)
The formatted DateTime.
For the following examples assume we have a variable named example_datetime that holds a DateTime that would format to the string "January 9th, 2007 9:41 AM PST".
If the user had the locale "fr-FR", or if we override the locale the final format will take that into account.
When communicating with external systems you may find the need to convert a datetime into an ISO 8601 or RFC3339 compatible timestamp, simply omit the format string and the resulting string will be in this format.
A date in the U.S. is commonly presented with numbers for the month, day, and year separated by slashes.
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.
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.
The time alone can be formatted in a familiar fashion with the format string "h:mm a".
A timezone parameter can be added as well to format the datetime to a specific timezone.
Note that even though do would normally format as the day of the week, and s would normally format as the second of the DateTime, in the text Today is they are not formatted because they are between brackets.
In many of these examples, there are characters in the format string in addition to the date units listed in the . The last example included both the colon character and a space character. Any characters that are not a date unit, are passed through in the final formatting. If we wanted to include a message in the format we would need to enclose the message in brackets ('****[' and '****]'). Any text inside the brackets is not interpreted as a date unit.
The function is the opposite of FORMAT_DATETIME. Rather than converting a DateTime to a String, it converts a String into a DateTime by parsing with a given format string.