Oracle SQL query works in SQL Developer but not QlikView

[sifatnabil]

Hi all,

On Oracle SQL Developer, this query runs successfully:

select * from table

where msg_date_time BETWEEN to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 05:00:00', 'DD-MON-YY hh24:mi:ss')

                        AND to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 18:00:00', 'DD-MON-YY hh24:mi:ss')

But when I put this in the QlikView script, I get a "Not a valid month" error. Any ideas on why this would happen?

[SreeniJD]

Hi There,

QV supports its native SQL which is almost similar to SQL. To change a date format, you can use DATE# function instead of to_date .. Also To_timestamp should be replaced with TIMESTAMP and BETWEEN shuldbe replaced with ~ INTERVALMATCH...

Let me know if you want this query in QV format.

HTH,

Sreeni

[sifatnabil]

Hi, I want to use this query in the SQL SELECT portion of the Qlikview script, i.e.

testtable:

load *;

SQL SELECT * FROM TABLE

where msg_date_time BETWEEN to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 05:00:00', 'DD-MON-YY hh24:mi:ss')

                        AND to_timestamp(to_date('23-SEP-15', 'DD-MON-YY') || ' 18:00:00', 'DD-MON-YY hh24:mi:ss')

It's the bolded portion of the query that works on SQL Developer but not in the SQL SELECT statement of the QV script.

[priyarane]

try to change from MON to MMM

[settu_periasamy]

Hi,

Have you tried without to_date? Like

to_timestamp('23-SEP-15 05:00:00', 'DD-MON-YY HH24:MI:SS')

[SreeniJD]

try this..

TIMESTAMP#('23-SEP-15','DD-MMM-YY HH:MM:SS')

[sasiparupudi1]

I think it is something to do with the timestamp format..

you shall have to execute the following sql before you can use your select. In SQL developer it is already set and therefore the sql works striaghtaway.

alter session set NLS_DATE_FORMAT='yyyy-MON-dd hh24:mi:ss';

hth

Sasi

[sifatnabil · Accepted solution]

Thanks for all your replies guys. It looks like to_date doesn't work here. to_char works in place of it though. Using alter session set NLS_DATE_FORMAT='yyyy-MON-dd hh24:mi:ss'; also doesn't work. Essentially, date conversions inside to_timestamp() don't seem to work in QV.

So the  query that works is:

where msg_date_time BETWEEN to_timestamp('23/09/2015' || ' 05:00:00', 'dd/mm/yyyy hh24:mi:ss')

                        AND to_timestamp('23/09/2015' || ' 18:00:00', 'dd/mm/yyyy hh24:mi:ss')

Or if using sysdate, use to_char:

where msg_date_time BETWEEN to_timestamp(to_char(sysdate,'dd/mm/yyyy') || ' 05:00:00', 'dd/mm/yyyy hh24:mi:ss')

                        AND to_timestamp(to_char(sysdate,'dd/mm/yyyy') || ' 18:00:00', 'dd/mm/yyyy hh24:mi:ss')