DATA Step, Macro, Functions and more

Time stamp format help

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 85
Accepted Solution

Time stamp format help

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.


Accepted Solutions
Solution
‎09-12-2013 08:19 AM
Frequent Contributor
Posts: 75

Re: Time stamp format help

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

View solution in original post


All Replies
Super Contributor
Posts: 297

Re: Time stamp format help

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.;

Solution
‎09-12-2013 08:19 AM
Frequent Contributor
Posts: 75

Re: Time stamp format help

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

Contributor
Posts: 32

Re: Time stamp format help

What is the format of CREATED_ON variable?

Frequent Contributor
Posts: 85

Re: Time stamp format help

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

Valued Guide
Posts: 2,175

Re: Time stamp format help

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.

Super User
Super User
Posts: 6,502

Re: Time stamp format help

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:MISmiley FrustratedS')

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1542 views
  • 3 likes
  • 6 in conversation