Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
shane_spencer
Valued Contributor

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

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?

1 Solution

Accepted Solutions
MVP
MVP

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

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

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

4 Replies
MVP
MVP

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

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:MISmiley FrustratedS') from dual

select to_char(trunc(sysdate), 'DD-MM-YYYY HH:MISmiley FrustratedS') 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

shane_spencer
Valued Contributor

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

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.

MVP
MVP

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

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

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

shane_spencer
Valued Contributor

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

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.

Community Browser