Conversion Functions

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.

SpecifierDescription

%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

Last updated

#930: Cross account hive GCP

Change request updated