Datatime format from oracle DB timestamp with timezone

[morrowcode]

Hey All, 

Long-time lurker, first-time poster. Have a question regarding DateTime formats and Oracle Database timestamps.

I've read many of the previous post regarding this and I really can't quite figure out where I'm going wrong.

I have a DateTime, 'last_changed_date', on the Oracle DB in this format 01/06/2019 15:56:48.925435 +01:00. According to the data in TOAD, its a timestamp(6) with timezone. 

Now if I run a query, with no formatting on this, I get errors in the log "Invalid numeric data, Printed='01-JUN-19 03.56.48.925435 PM +01:00'

This isn't great for me as I use this data in a CSV file and doesn't quite work.

I've tried 

data have;
set want;

LAST_CHANGED_DATE1=input(LAST_CHANGED_DATE,DATETIME.);
format LAST_CHANGED_DATE1 DATETIME19.;

run;

...But everything is converted to 12hr time and lose the AM/PM 

I've also tried formatting as below and still no joy.

proc format;
picture dtpic
other='%d-%m-%Y %0H.%0M.%0S.%0F %p %Z' (datatype=datetime);
run;

data have;
set want;
LAST_CHANGED_DATE = datetime();
format LAST_CHANGED_DATE dtpic.;
run;

Ideally I want the datetime (last_changed_date) in the format as it is on the oracle DB (01/06/2019 15:56:48.925435 +01:00)

I'm really not sure what to do, and would appreciate any help!

Many thanks!

Colin

[Tom]

Are you connecting to the database directly from SAS?  How do the datetime values come across to SAS? Show the code you ran and if any errors the LOG. (Make sure to sue the Insert SAS Code button to insert the lines of code to paste the lines of code into the separate pop-up window to prevent forum from flowing the code as if it were paragraphs)

Or are you writing the database values into a text file in that format and then trying to read the text file?  If so post example data file (make sure to use the Insert Code button .)  So what code you used to try to read the example data.  You will probably need to read the string in parts to be able to pull out the date, time and offset into separate strings to convert into numbers.

SAS does not have any way to store a separate timezone setting for a datetime value. Datetime values are just stored as the number of seconds since 1960.  If you want to retain that level of detail of the data then you will need to put it into a separate variable.

[Patrick]

@morrowcode 

I don't have an environment with Oracle to run some tests so here just based on documentation.

According to the doc you can read an Oracle Timestamp with Timezone value into SAS but it will get stored as a string in a character variable. You can find this here under section LIBNAME Statement: Default SAS Formats for Oracle Data Types

TIMESTAMP WITH TIMEZONE		$w.

I don't know how this string will actually look like (please share with us if you can) but it's eventually already in the form you need it so you could write it straight to your .csv.

If you need the string converted to a SAS DateTime value then you need to read it into a numerical variable using the appropriate informat. There are Informats and Formats for time offsets. The docu here  here might give you a start.

If you just need everything converted to your local SAS Session time then another way to go would be to use explicit pass-through SQL. You then could cast the column to a simple TIMESTAMP column which you then load into SAS. This way Oracle will ensure correct conversion and you'd be getting directly a SAS Datetime column on the SAS side - but you wouldn't have the time offset anymore. As @Tom proposed you could store this information in a 2nd variable, eventually also already created on the Oracle side via explicit pass-through SQL. 

To go down such a route here the docu link.