Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
let vYestertay = floor(today(1)-1);
SQL select * from HFM_ERRORLOG where DTIMESTAMP>= $(vYestertay);
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);
or
SQL select * from HFM_ERRORLOG where DTIMESTAMP>= trunc(sysdate-1);
42166 is the Qlik date, I think not a valid date for Oracle
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.
let vYestertay = floor(today(1)-1);
SQL select * from HFM_ERRORLOG where DTIMESTAMP>= $(vYestertay);
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.