where expression in exctration - datetime field

Reply
Contributor
Posts: 27

where expression in exctration - datetime field

Hi,

I'm extracting data from an Oracle table and using SAS Data Integration Studio (DI).

The Oracle expression in the where condition is:

 

DATA_CARICAMENTO >= TRUNC(SYSDATE - 1, 'MI') AND DATA_CARICAMENTO <= SYSDATE

I would use an equivalent expression in the Extract transformation of DI.

 

DATA_CARICAMENTO's Format is Datetime20.

Do you know what's a correct expression I can use?

 

Thanks.

Super User
Posts: 7,447

Re: where expression in exctration - datetime field

A little googling for "Oracle trunc function" revealed that this truncates the value to the minute.

So I guess that the low end for the comparison is the minute of 1 second past.

In SAS, that would be

today() * 86400 + intnx('minute',time()-1,0,'begin')

if I guess right.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,388

Re: where expression in exctration - datetime field

IMO an easier way to do it (and probably more effective) is to use tha autoamtic date/time variables.

"&SYSDATE:&SYSTIME"dt

&SYSTIME is in minutes, no need for trucation logic...

Data never sleeps
Super User
Posts: 7,447

Re: where expression in exctration - datetime field

[ Edited ]

Caution: &sysdate and &systime contain the date and time when the SAS session was started, not the current date and time!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,388

Re: where expression in exctration - datetime field

Agree, but usually the Extract step is in the beginning of the session.

But in DI Studio, there's also automatic datetime macro variables created for you, so my final (?) suggestion would be to use ETLS_STARTTIME (or similar name, don't have DI Studio at my fingertips). 

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 190 views
  • 0 likes
  • 3 in conversation