Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
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
maxgro
MVP
MVP

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

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

View solution in original post

4 Replies
maxgro
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

shane_spencer
Specialist
Specialist
Author

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.

maxgro
MVP
MVP

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

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

shane_spencer
Specialist
Specialist
Author

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.