The DATE_FROM_FORMAT function takes a string representation of a date and converts it into a date data type.
This function requires a single string, representing a date, as input. By default, the DATE_FROM_FORMAT function will process this string as though it is in the ISO 8601 Format ("YYYY-MM-DDThh:mm:ssZ") but another optional input string can be given to define the format differently. This function then returns the given date as a date data type.
Declaration
DATE_FROM_FORMAT(date_string, format_options, time_zone_string) -> date
Parameters
date_string (required, type: string)
A string representing a date.
format_options (optional, type: string, default: ISO 8601 Format: "YYYY-MM-DDThh:mm:ssZ")
A string defining the format of the time to be converted.
Return Values
date (type: date)
The time described in date_string, as a date data type.
Examples
The following example takes the string "2007-01-09T09:41:00" and converts the date represented by that string into the date data type. Note that the values associated with the returned day, month, and year match the hour and minute given by the input string:
DATE_FROM_FORMAT("2007-01-09T09:41:00") -> {"day": 9, "month": 1, "year": 2007}
The time described in ISO 8601 Format in the above example is in simple UTC, but this does not have to be the case. DATE_FROM_FORMAT will return a date adjusting for whatever timezone is given. The follow example describes a time with a timezone offset of +12 hours. Note how this is taken into account by returning a date a day before the day given – twelve hours before 9:41 in the morning is 21:41 the previous day:
DATE_FROM_FORMAT("2007-01-09T09:41:00+12:00") -> {"day": 8, "month": 1, "year": 2007}
When not dealing with a timezone offset, full ISO 8601 Format provides a lot of information that is not necessary for returning a valid date data type. ISO 8601 Format allows this portion to be removed; the following example takes "2007-01-09" and converts it into a date data type just like in the first example:
DATE_FROM_FORMAT("2007-01-09") -> {"day": 9, "month": 1, "year": 2007}
There are, however, other ways to write a date. "01-09-2007" describes the same time using another formatting convention (two digits designating the month, a dash, two digits designating the day, another dash, four digits designating the year, a space, ), but because it is not in ISO 8601 Format, DATE_FROM_FORMAT cannot interpret it without more information on how the input string ought to be parsed:
DATE_FROM_FORMAT("01-09-2007") -> NULL
In order to designate how the string "01-09-2007" can be properly interpreted as a date_string, a value must be given for format_options. As discussed above, the string "01-09-2007" designates the time in the format "MM-DD-YYYY" (two digits designating the month, a dash, two digits designating the day, another dash, and four digits designating the year), and when format_options declares this, as in the following example, DATE_FROM_FORMAT can interpret "01-09-2007" as a date_string:
DATE_FROM_FORMAT("01-09- 2007", "MM-DD-YYYY") -> {"day": 9, "month": 1, "year": 2007}
As long as the format is designated by format_options in accordance with the conventions discussed in this table, DATE_FROM_FORMAT can parse a myriad of differently formatted dates and times. The following example takes the string "Jan 07 9th", designates that it should be parsed in the rather unconventional format "MMM - YY Do" (that is, an abbreviation of the month, a space, a dash, another space, the last two digits of the year, and the ordinal number defining the day of the month), and then outputs the designated date as a date data type:
DATE_FROM_FORMAT("Jan - 07 9th", "MMM - YY Do") -> {"day": 9, "month": 1, "year": 2007}
Discussion
The examples given above regarding how date_string may be formatted are far from comprehensive, as any and all combinations of the tokens given in this table can be used to describe valid ways of parsing strings that indicate particular dates. For further discussion and examples of how these tokens can be applied, check out FORMAT_DATE and FORMAT_DATETIME.