Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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);

View solution in original post

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: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

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);

View solution in original post

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.