Note
This function is available starting with V5.0.1.
Declaration
parseDateTime(str [, format])
Description
The parseDateTime function parses a string into a DATETIME type according to the specified format. If the format parameter is omitted, the default format %Y-%m-%d %H:%i:%s (i.e., YYYY-MM-DD HH:MM:SS) is used to parse the string.
Notice
This is a ClickHouse-compatible function. By default, it is not available. To use it, you must set the sql_func_extension_mode parameter to enable it.
Parameters
Parameter |
Required/Optional |
Description |
|---|---|---|
| str | Required | The date and time string to be parsed. |
| format | Optional | Format string, using%The format specifier at the beginning specifies the parsing rules. If omitted, the default format is used.%Y-%m-%d %H:%i:%s(That is,YYYY-MM-DD HH:MM:SS). |
Common format specifiers include:
Format specifier |
Description |
|---|---|
| %Y | Four-digit year |
| %y | Two-digit year |
| %m | Month (01-12) |
| %d | Date (01-31) |
| %H | Hour (00-23) |
| %i | Minute (00-59) |
| %s | Seconds (00-59) |
| %f | microsecond |
| %F | ISO date format (YYYY-MM-DD) |
| %T | ISO Time Format (HH:MM:SS) |
Response parameters
- Returns a
DATETIMEtype. - If any parameter is
NULL, the function returnsNULL. - An error is reported if the string does not match the format or if the date and time value is invalid.
Examples
Enable the ClickHouse extension function mode.
obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";Parse the date and time string using the default format.
obclient> SELECT parseDateTime('2023-12-08 14:30:45') AS dt;The return result is as follows:
+---------------------+ | dt | +---------------------+ | 2023-12-08 14:30:45 | +---------------------+ 1 row in setParse date and time strings using explicit formats.
obclient> SELECT parseDateTime('12/08/2023 02:30:45 PM', '%m/%d/%Y %h:%i:%s %p') AS dt;The return result is as follows:
+---------------------+ | dt | +---------------------+ | 2023-12-08 14:30:45 | +---------------------+ 1 row in set