topic Oracle (SQL) Database - selecting / extracting a date (number) in QlikView

Oracle (SQL) Database - selecting / extracting a date (number)

shane_spencer — Fri, 12 Jun 2015 12:20:44 GMT

I've got an Oracle database I'm pulling data from. I run this query to get yesterdays data:

SQL select * from HFM_ERRORLOG where DTIMESTAMP>= 42166.0;

and get the following:

42166 is 11/06/2015; if I put '11/06/2015' in to excel and convert the format of the cell to Number then '42166' is the number I get. The number of days since 1900 I understand. I need to modify the above select command to use a variable for "yesterdays date" so I can automate it, instead of hard-coding the number in.

Anyone know how to do this?

Re: Oracle (SQL) Database - selecting / extracting a date (number)

maxgro — Fri, 12 Jun 2015 12:29:35 GMT

In oracle sysdate is the date and time, try this with an oracle tool (sqlplus, toad, sql developer, etc, ....)

select to_char((sysdate), 'DD-MM-YYYY HH:MI:SS') from dual

select to_char(trunc(sysdate), 'DD-MM-YYYY HH:MI:SS') from dual

so your statement should be (if DTIMESTAMP ia an Oracle date)

SQL select * from HFM_ERRORLOG where DTIMESTAMP>= (sysdate-1);

SQL select * from HFM_ERRORLOG where DTIMESTAMP>= trunc(sysdate-1);

42166 is the Qlik date, I think not a valid date for Oracle

Re: Oracle (SQL) Database - selecting / extracting a date (number)

shane_spencer — Fri, 12 Jun 2015 12:46:06 GMT

I have tried that. I think the 42166 is how the date is stored in the Oracle DB. As a NUMBER and using sysdate gives a DATE format so it fails.

Re: Oracle (SQL) Database - selecting / extracting a date (number)

maxgro — Fri, 12 Jun 2015 13:10:34 GMT

let vYestertay = floor(today(1)-1);

SQL select * from HFM_ERRORLOG where DTIMESTAMP>= $(vYestertay);

Re: Oracle (SQL) Database - selecting / extracting a date (number)

shane_spencer — Fri, 12 Jun 2015 13:23:57 GMT

Thank you. I did not realise I could combine the QlikView variable in the SQL select statement. I use similar variables all the time but this is the first time I've used Oracle as a source.