All Forums Analytics
karmakar.amitkr 1 post Joined 09/11
02 Jul 2012
Extracing ZONE part from a TIMESTAMP(0) WITH TIME ZONE column

In table definition the below column is mentioned like:

Lcl_Dttmz TIMESTAMP(0) WITH TIME ZONE FORMAT 'yyyy-mm-ddbhh:mi:ss' TITLE 'Sale Location Local Dttmz ' COMPRESS

Value retriving looks for the same is like '2010-08-26 14:42:45+10:00' .Want to cast the value as VARCHAR(25) to extract the zone part.

But cast(Lctn_Lcl_Dttmz as varchar(25)) is retriving value like '2010-08-26 14:42:45' .

Can anyone plz help me out how to extract the zone part of the particular value..

ulrich 816 posts Joined 09/09
02 Jul 2012

Check the extract function. From the manual:

 

Extracts a single specified full ANSI SQL:2008 field from any DateTime or Interval value,

converting it to an exact numeric value.

 

 

 

Syntax element … Specifies …

YEAR that the integer value for YEAR is to be extracted from the date

represented by value.

MONTH that the integer value for MONTH is to be extracted from the date

represented by value.

DAY that the integer value for DAY is to be extracted from the date represented

by value.

HOUR that the integer value for HOUR is to be extracted from the date

represented by value.

MINUTE that the integer value for MINUTE is to be extracted from the date

represented by value.

TIMEZONE_HOUR that the integer value for TIMEZONE_HOUR is to be extracted from the

date represented by value.

TIMEZONE_MINUTE that the integer value for TIMEZONE_MINUTE is to be extracted from

the date represented by value.

SECOND that the integer value for SECOND is to be extracted from the date

represented by value.

value an expression that results in a DateTime, Interval, or UDT value.

FF07D144

EXTRACT

MONTH

( YEAR FROM value)

DAY

HOUR

MINUTE

SECOND

TIMEZONE_HOUR

TIMEZONE_MINUTE

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ocirej23 10 posts Joined 04/11
02 Jul 2012

 

I tried doing it like this:

 

SELECT EXTRACT(TIMEZONE_HOUR FROM CAST('2010-08-26 14:42:45+10:00' AS TIMESTAMP(0) WITH TIME ZONE));

Result:

10

 

HTH

Cheers,
Eco

ulrich 816 posts Joined 09/09
03 Jul 2012

 

SELECT substr(cast(CAST('2010-08-26 14:42:45-08:00' AS TIMESTAMP(0) WITH TIME \
ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(F)Z') as char(25)),20,6);

would do the substr

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.