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
... View more
