Time stamp format help

[forumsguy]

Hi All,

I have a Terdata table which has field created_on with timestamp(0) format of Teradata.

I need to select rows from table using my DI Job.. however I am unable to find equivalent SAS format.

E.G.

My entry in TD table is sel * from tab where created_on<='2013-09-12 12:00:00' ---------> This works

In DI JOb's precode , I am writing

%let bus_time=%sysfunc(datetime(),DATETIME23.3);

%let bus_time_f=%unquote(%bquote(')&bus_time%bquote('));

and then selecting in my SQL join as

sel * from tab where created_on<=&bus_time_f--------> This doesnt work.

bus_time_f  resolves to '12SEP2013:20:54:14.087'   ......

Need some assistance in getting correct format.

[Scott_Mitchell]

Hi ForumsGuy,

I have no experience with the DB to which you refer, so I am not sure if the following will help, but you could use a picture format.

PROC FORMAT ;

PICTURE TERDATE LOW-HIGH = '%Y-%0m-%0d:%0H:%0M:%0S'

(DATATYPE = DATETIME) ;

RUN ;

%LET BUS_TIME=%SYSFUNC(INPUTN(%SYSFUNC(DATETIME()),TERDATE.));

%put  &BUS_TIME.;

[dhana · Accepted solution]

You can try this also ...!

%LET VAL = %SUBSTR(%SYSFUNC(DATETIME(),DATETIME23),1,9);
%LET BUS_DATE = %SYSFUNC(INPUTN(&VAL,DATE9.),YYMMDD10.);
%LET BUS_TIME=%SUBSTR(%SYSFUNC(DATETIME(),DATETIME23),11,8);
%LET BUS_TIME_F=%UNQUOTE(%BQUOTE(')&BUS_DATE:&BUS_TIME%BQUOTE('));

%PUT #### &VAL #### ;
%PUT #### &BUS_DATE #### ;
%PUT #### &BUS_TIME #### ;
%PUT #### &BUS_TIME_F #### ;

Use the variable BUS_TIME_F in your code.

Thanks

Dhana

[sandyming]

What is the format of CREATED_ON variable?

[forumsguy]

Amazing.. Both of these worked.. Thanks Dhana and Scott

[Peter_C]

If the context where the code will run is "explicit pass through" then Tom's use if the Teradata format looks most effective !

If "implicit pass through" then there should be no need for special arrangements as SAS automatically  understands and handles the datatypes like datetime constants.

Of course it has been some time since I programmed queries and user transforms in SAS DI.

[Tom]

You can tell Teradata how to interpret the string you have given it.

In SAS generate the string in normal DATETIME format.

%let bus_time=%sysfunc(datetime(),DATETIME19.);

%let bus_time_f=%unquote(%bquote(')&bus_time%bquote('));

In Teradata tell what format to use to read that format.

sel *

from tab

where created_on <= &bus_time_f (timestamp, format 'DDMMMYYYY:HH:MI:SS')