ToOffsetDateTime

The ToOffsetDateTime function converts the input string, date, or date_time to the offset_date_time data type. The offset_date_time data type allows the use of UTC timezone offset information with time values and is based on Java’s OffsetDateTime class (https://docs.oracle.com/javase/8/docs/api/java/time/OffsetDateTime.html).

Syntax

ToOffsetDateTime(<String>|<date>|<date_time>|<zoned_date_time>|<instant> [,<offset_date_time_mask>])

Return Type

offset_date_time

Details

Offset_date_times contain UTC timezone offset information lacking from other data types such as date_time and instant. Unless you need to store values with different timezones, Xactly recommends using the instant data type instead.

Examples

eval ToOffsetDateTime('September 22, 2016 Thursday 03:24:29 +00:00', 'MMMM dd, yyyy EEEE HH:mm:ss XXXXX');

tooffsetdatetime_01

select ToOffsetDateTime('September 22, 2016 Thursday 03:24:29 +00:00', 'MMMM dd, yyyy EEEE HH:mm:ss XXXXX') as zulu_example,
ToOffsetDateTime('September 22, 2016 Thursday 03:24:29 +03:00', 'MMMM dd, yyyy EEEE HH:mm:ss XXXXX') as zulu_plus_3_hours_example
from Empty();

tooffsetdatetime_02

select Now() as now,
TypeNameOf(Now()) as TypeNameOf_now,
ToOffsetDateTime(Now()) as cast_to_offset_date_time,
TypeNameOf(ToOffsetDateTime(Now())) as TypeNameOfCast
from Empty();

tooffsetdatetime_03

select CurDateTime() as cur_date_time,
TypeNameOf(CurDateTime()) as TypeNameOf_cur_date_time,
ToOffsetDateTime(CurDateTime()) as cast_to_offset_date_time,
TypeNameOf(ToOffsetDateTime(CurDateTime())) as TypeNameOfCast
from Empty();

tooffsetdatetime_04

Related Functions

FormatDateTime

ToDate

ToDateTime

ToZonedDateTime

WithZDefault

WithZOffset