Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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
Highlighted
MVP
MVP

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

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

View solution in original post

4 Replies
Highlighted
MVP
MVP

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

Highlighted
Specialist
Specialist

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.

Highlighted
MVP
MVP

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

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

View solution in original post

Highlighted
Specialist
Specialist

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.