FORMAT_DATETIME

The FORMAT_DATETIME function converts a DateTime into a String 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 Data Tables.

Declaration

FORMAT_DATETIME(datetime, format_string, locale, time_zone) -> formatted_datetime_string

Parameters

datetime (required, type: DateTime) The DateTime to be formatted.

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. 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 LCID String. 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 TZ database names, or FORMAT_DATETIME will not parse them as meaningful timezones.

Return Values

formatted_datetime_string (type: string) The formatted DateTime.

Examples

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

FORMAT_DATETIME(  
  example_datetime,   
  "MMMM Do, YYYY h:mm A z"
) -> "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.

FORMAT_DATETIME(  
example_datetime,   
"MMMMM Do, YYYY h:mm A z",  
"fr-FR"
) -> "janvier 1er, 2007 1:41 am PST"

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.

FORMAT_DATETIME(example_datetime) -> "2007-01-09T09:41:00-08:00"

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

FORMAT_DATETIME(example_datetime, "MM/DD/YYYY") -> "01/09/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_DATETIME(example_datetime, "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_DATETIME(example_datetime, "YYYY/DD/MM") -> "2007/01/09"

The time alone can be formatted in a familiar fashion with the format string "h:mm a".

FORMAT_DATETIME(example_datetime, "h:mm a") -> "9:41 am"

A timezone parameter can be added as well to format the datetime to a specific timezone.

FORMAT_DATETIME(  
   example_datetime,  
   "MM/DD/YY h:mm A z",  
   "en-EN",  
   "America/Chicago"  
) -> 01/09/07 9:41 AM CDT

In many of these examples, there are characters in the format string in addition to the date units listed in the data tables. 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.

FORMAT_DATETIME(  
  example_datetime,   
 "[Today is] MMMM Do, YYYY h:mm A z"  
) -> "Today is January 9th, 2007 9:41 AM PST"

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.

Discussion

The DATETIME_FROM_FORMAT 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.

FORMAT_DATETIME(NOW(), "DD/MM/YYYY")