The DATETIME_FROM_FORMAT function takes a string representation of a date and converts it into a DateTime.
This function requires a single string, representing a date and time, as input. By default, the DATETIME_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 and time as a DateTime.
Declaration
DATETIME_FROM_FORMAT(datetime_string, format_options) -> datetime
Parameters
datetime_string (required, type: string)
A string representing a date and time.
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
datetime (type: DateTime)
The time and date described in datetime_string, as a DateTime.
Examples
The following example takes the string "2007-01-09T09:41:00" and converts the date and time represented by that string into a DateTime. Note that the values associated with the returned day, month, year, and time match the day, month, year, and time given by the input string:
DATETIME_FROM_FORMAT("2007-01-09T09:41:00") -> {
"date": {
"day": 9,
"month": 1,
"year": 2007
},
"time": {
"hour": 9,
"minute": 41,
"second": 0,
"millisecond": 0
},
"timeZone": "UTC"
}
The time described in ISO 8601 Format in the above example is in simple UTC, but this does not have to be the case. DATETIME_FROM_FORMAT will return a date and time 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:
DATETIME_FROM_FORMAT("2007-01-09T09:41:00+12:00") -> {
"date": {
"day": 8,
"month": 1,
"year": 2007
},
"time": {
"hour": 21,
"minute": 41,
"second": 0,
"millisecond": 0
},
"timeZone": "UTC"
}
In the above examples, datetime_string described a date in ISO 8601 Format, but there are other ways to describe a date. "01-09-2007, 9:41 am" describes the same date and time 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 comma, a space, as many digits as are required to designate the hour, a colon, two digits to designate the minute, a space, and then a lowercase designation of AM or PM), but because it is not in ISO 8601 Format, DATETIME_FROM_FORMAT cannot interpret it without more information on how the input string ought to be parsed:
DATETIME_FROM_FORMAT("01-09-2007, 9:41 am") -> NULL
In order to designate how the string "01-09-2007, 9:41 am" can be properly interpreted as a datetime_string, a value must be given for format_options. As discussed above, the string "01-09-2007, 9:41 am" designates the time in the format "MM-DD-YYYY, h:mm a", and when format_options declares this, as in the following example, DATETIME_FROM_FORMAT can interpret "01-09-2007, 9:41 am" as a datetime_string:
DATETIME_FROM_FORMAT("01-09-2007, 9:41 am", "MM-DD-YY, h:mm a") -> {
"date": {
"day": 9,
"month": 1,
"year": 2007
},
"time": {
"hour": 9,
"minute": 41,
"second": 0,
"millisecond": 0
},
"timeZone": "UTC"
}
Discussion
The examples given above regarding how datetime_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.