Date time issues

Reply
Contributor
Posts: 27

Date time issues

%PUT START_DATE = &START_DATE;     Initially start_date has value 19084 which is numeric value for 01Apr2012

%PUT END_DATE = &END_DATE;         end_date has value 19413 which is numeric value for 24FEB2013

%LET _START_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(&START_DATE,DDMMYY10.))%STR(%')));

%LET _END_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(&END_DATE,DDMMYY10.))%STR(%')));

%PUT _START_DATE = &_START_DATE;  _START_DATE HAS VALUE '01/04/2012'

%PUT _END_DATE = &_END_DATE;     _END_DATE HAS VALUE '24/02/2013'

i am using an oracle query through sql pass thorugh to fetch data exists between these two dates using below statement in the where clause

WHERE TO_DATE(TO_CHAR(YD.YLD_UPLOAD_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE(&_START_DATE.,'DD/MM/YYYY' ) AND TO_DATE(&_END_DATE.,'DD/MM/YYYY')

Now i am using a different case. where instead of only dates start_date and end_date has date time values.

%PUT START_DATE = &START_DATE;     Initially start_date has value 1648857600 which is numeric value for 01Apr2012:00:00:00

%PUT END_DATE = &END_DATE;         end_date has value 1677888000 which is numeric value for 03MAR2013:00:00:00

BUT I NEED TO PASS THE DATE VALUES IN THE UPPER FORMAT ONLY TO THE ORACLE QUERY I.E. '01/04/2012' and  '03/03/2013'

SO WHAT SHOULD I WRITE IN THE BELOW TWO STATEMENTS TO PERFORM THIS

%LET _START_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(&START_DATE,DDMMYY10.))%STR(%')));

%LET _END_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(&END_DATE,DDMMYY10.))%STR(%')));

Plz guys help me out

Super User
Posts: 5,429

Re: Date time issues

Posted in reply to rohitguptaecb

Do you really need to do a explicit pass-thru? Implict seems much easier, the Libname engine will do the translation for you.

Data never sleeps
Contributor
Posts: 27

Re: Date time issues

Yes LinusH, The code for the ETL job has been written this way. I have to follow the same code guide lines. Is there any way to get the dates in the same format while using datetime values instead of dates values ?

Super Contributor
Posts: 276

Re: Date time issues

Posted in reply to rohitguptaecb

Hi..

Have you tried with Datepart Function???.That might be helpful..

Regards.

Sanjeev.K

Contributor
Posts: 27

Re: Date time issues

Posted in reply to kuridisanjeev

i tried to use date part

%LET _START_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(%SYSFUNC(DATEPART(&START_DATE),DATETIME20.)))%STR(%')));
%LET _END_DATE = %SYSFUNC(CAT(%STR(%')%SYSFUNC(PUTN(%SYSFUNC(DATEPART(&END_DATE),DATETIME20.)))%STR(%')));

Giving errors..

Super Contributor
Posts: 276

Re: Date time issues

Posted in reply to rohitguptaecb

If possible can i see what error you getting while using Date part..??

Regards,

Sanjeev.K

Super User
Super User
Posts: 7,050

Re: Date time issues

Posted in reply to rohitguptaecb

It will work easier if you break it into pieces. 

%let start_date=01Apr2012:00:00:00;

To reference this as a number you can use datetime literal syntax:  "&start_date"dt

To get the datepart you could apply the DATEPART() function to it, but since it is macro you need to use %SYSFUNC() to do that.

Since you want the value in m/d/y format you can do that as part of the same statement by adding an  output format to the %SYSFUNC() macro function call.

%let _start_date=%sysfunc(datepart("&start_date"dt),ddmmyy10.);


Now add the surrounding single quotes.

%let _start_date=%str(%')&_start_date%str(%');

4    %put start_date=&start_date ;

start_date=01Apr2012:00:00:00

5    %put _start_date=&_start_date ;

_start_date='01/04/2012'

Super Contributor
Posts: 578

Re: Date time issues

Posted in reply to rohitguptaecb

I usually get confused with all of the macro coding and resort to a data step.  I also add the to_date function for oracle as it helps prevent interpretation errors:

%let start_date=19084;

%let end_date=19413;

%PUT START_DATE = &START_DATE;

%PUT END_DATE = &END_DATE; 

data _null_;

call symput('_START_DATE',"to_date('"||put(&START_DATE,DATE9.)||"','DDMonYYYY')");

call symput('_END_DATE',"to_date('"||put(&END_DATE,DATE9.)||"','DDMonYYYY')");

run;

%put _START_DATE=&_START_DATE;

%PUT _END_DATE=&_end_date;

results in

24     %put _START_DATE=&_START_DATE;

_START_DATE=to_date('01APR2012','DDMonYYYY')

25     %PUT _END_DATE=&_end_date;

_END_DATE=to_date('24FEB2013','DDMonYYYY')

If you have datetime instead of date, just change the date9 format to dtdate9.

if start/end dates are datetime, then just change the date9. format to dtdate9.

Super User
Super User
Posts: 7,050

Re: Date time issues

Posted in reply to rohitguptaecb

This should work for DateTime values.

%let start_date=1648857600;

%let end_date=1677888000 ;

%let _start_date = %str(%')%sysfunc(datepart(&start_date),ddmmyy10.)%str(%');

%let _end_date = %str(%')%sysfunc(datepart(&end_date),ddmmyy10.)%str(%');

I like 's idea of generating the TO_DATE function call.  In that case you might want to use the DEQUOTE() function to make it easier to insert the single quotes and eliminate the need for using %str(%').  When the nested commands get so long I find it easier to code/read/debug if I break out the inner function calls as separate %LET statements.

%let start_date=1648857600;

%let end_date=1677888000 ;


%let _start_date =%sysfunc(datepart(&start_date),ddmmyy10.);

%let _start_date =%sysfunc(dequote("todate('&_start_date','DD/MM/YYYY')"));


%let _end_date =%sysfunc(datepart(&end_date),ddmmyy10.);

%let _end_date =%sysfunc(dequote("todate('&_end_date','DD/MM/YYYY')"));

Contributor
Posts: 27

Re: Date time issues

Thanks Tom, your response is very much helpful.. it worked ..

Ask a Question
Discussion stats
  • 9 replies
  • 800 views
  • 7 likes
  • 5 in conversation