Hi,

Newbie need some help with calculations:
COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - &DATE;

I found that DATE_FROM_ORACLE_SQL is timestamp, noted to do calculations in SQL and so on.
I test it with DATEDIF(HDATE(TERMINAS, 'YYMD'), &DATE, 'D') and similar,
but with no results.

Thanks in advance

Convert today's date to a date-time value and then use HDIFF with the SQL date-time column (DATE_FMT) and today's date as a date-time value (DT_TODAY1),

OR

Convert the SQL date-time column to a date column and then use DATEDIF with the date column (DATE_FMT2) and today's date (DT_TODAY2) as a date value:

TABLE FILE TIME
PRINT
COMPUTE DT_TODAY1/HYYMDS = HGETC(10, 'HYYMDS');
DATE_FMT
COMPUTE NUMBER_OF_DAYS1/D5 = HDIFF(DT_TODAY1, DATE_FMT, 'DAY','D5');

COMPUTE DT_TODAY2/YYMD = '&YYMD';
COMPUTE DATE_FMT2/YYMD = HDATE(DATE_FMT, 'YYMD');

COMPUTE NUMBER_OF_DAYS2/I6 = DATEDIF(DATE_FMT2, DT_TODAY2, 'D');
WHERE READLIMIT EQ 1
END

Vidas,

When you created the MASTER for your Oracle table, it probably defined DATE_FROM_ORACLE_SQL with the format HYYMDs which is the time-stamp. If you do not need the time you can change the format fo your field to:
FIELD=DATE_FROM_ORACLE_SQL,FORMAT=MDYY, ACTUAL=DATE...

Then in your program you can write:
COMPUTE NUMBER_OF_DAYS/I5 = DATE_FROM_ORACLE_SQL - '&DATEMDYY';

To expand on DANNY-SRL's comment, if like us, where sometimes you need the time and sometimes you don't just leave both in the .mas. Something like this:

FIELD=TSTDT,ORACLE_DTTM,YYMD,DATE,$
FIELD=TSTDTTM,ORACLE_DTTM,HYYMDS,HYYMDS,$

before you create your synonym, (if you're using the synonym wizard) you can use the setting:

ENGINE SQLORA SET DATETIME OFF

It works for SQL, should work for Oracle. That is if you don't need the time setting.

Carol

Thanks to all for answers,
For me, newbie, most undestandable expresion at this time is

TABLE FILE SQLOUT
PRINT
.............
COMPUTE TODAY_DATE_TIME/HYYMDS = HGETC(10, 'HYYMDS'); NOPRINT
COMPUTE DIFF_DAYS/D5 = HDIFF(TODAY_DATE_TIME, DATE_TIME_FROM_ORACLE, 'DAY', 'D5');
............