Conversion Functions

Transform data types and formats for compatibility.

Convert string to date or timestamp,

select date '2022-01-02';

select timestamp '2022-01-02';

DATE( <expr> )

The DATE function converts the input expression to a value of DATE data type. Supported Datatype: Date/Timestamp/String

> select date('2024-05-06');
2024-05-06

TIMESTAMP( <expr> )

The TIMESTAMP function converts the input expression to a value of TIMESTAMP data type.

Supported Datatype: Date/Timestamp/String

> select timestamp('2024-05-06 13:45:12');
2024-05-06T13:45:12.000+00:00

TO_DATE( <string_expr> [, <format> ] )

This function converts an input expression into a date.

Support datatype: Date/Timestamp/String

> select to_date('2024.07.23', 'yyyy.MM.dd')
2024-07-23

Usages:

1. We support java format string for format string. However time-zone ID and time-zone name is not supported.

2. TIMESTAMP with single parameter and Integer types in TO_DATE function is not supported

TO_TIMESTAMP( <string_expr> ) or TO_TIMESTAMP( <string_expr> [, <format> ] )

Returns the timestamp by parsing the given string expression.

> select to_timestamp('2024.07.23', 'yyyy.MM.dd');
2024-07-23T00:00:00.000+00:00

TO_TIMESTAMP_NTZ( <expr> )

Returns the timestamp by parsing the given string expression.

> select to_timestamp_ntz('1997-09-06 12:29:34');
1997-01-01 12:29:34

FROM_UNIXTIME_WITHUNIT( <expr>, <unit> )

Returns the UNIX timestamp value as a timestamp. The unit represents the expression value, whether it is in milliseconds or seconds.

• SECONDS - If this keyword unit is specified, the function considers the expression value in seconds

• MILLISECONDS - If this keyword unit is specified, the function considers the expression value in milliseconds

> select FROM_UNIXTIME_WITHUNIT(1673263327000, 'milliseconds')
'2023-01-09 11:22:07'

> select FROM_UNIXTIME_WITHUNIT(1674797653, 'seconds')
'2023-01-27 05:34:13'

TO_UNIX_TIMESTAMP( <expr> )

Returns the timestamp in expr as a UNIX timestamp. Output results will be in epoch milliseconds

> select to_unix_timestamp(cast('2000-05-08 09:12:10' as timestamp));
957777130000

PARSE FUNCTIONS

This section contains parse functions that uses the specifier (format_string). The table below contains the supported patterns for the parse functions.

Specifier	Description
%a or %W	Abbreviated weekday name (Sun .. Sat) or Weekday name (Sunday .. Saturday)
%b or %M	Abbreviated month name (Jan .. Dec) or Month name (January .. December)
%d or %e	Day of the month, numeric (01 .. 31) or numeric (1 .. 31), this specifier does not support 0 as a month or day.
%c or %m	Month, numeric (1 .. 12), this specifier does not support 0 as a month.
%f	Fraction of second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999), timestamp is truncated to milliseconds
%H or %k	Hour (00 .. 23)
%h or %I	Hour (01 .. 12)
%j	Day of year (001 .. 366)
%i	Minutes, numeric (00 .. 59)
%S or %s	Seconds (00 .. 59)
%r	Time of day, 12-hour (equivalent to %h:%i:%s %p)
%T	Time of day, 24-hour (equivalent to %H:%i:%s)
%v	Week (01 .. 53), where Monday is the first day of the week and we can use %y or %x for year for using %x use 4 digits for year
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits), when parsing, two-digit year format assumes range 1970 .. 2069, so “70” will result in year 1970 but “69” will produce 2069
%%	A literal % character

PARSE_DATE ( <format_string> , <date_string> )

Converts a string representation of a date to a DATE object.

> SELECT PARSE_DATE('%Y/%m/%d/%H','2022/10/20/05');
2022-10-20

PARSE_DATETIME ( <format_string> , <datetime_string> )

Converts a string representation of a date-time to a DATETIME object.

> SELECT PARSE_DATETIME('%a %M %e %Y %H:%i:%s', 'Thu December 25 2008 20:57:10')
25/12/2008 20:57:10

PARSE_TIMESTAMP ( <format_string> , <timestamp_string> [, <time_zone> ] )

Converts a string representation of a timestamp to a TIMESTAMP object.

> SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%i:%s','2022-01-02 12:27:11','America/New_York')
02/01/2022 17:27:11