Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sifatnabil
Valued Contributor

Oracle SQL query works in SQL Developer but not QlikView

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?

1 Solution

Accepted Solutions
sifatnabil
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

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

7 Replies
jaaldurgam
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

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
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

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
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

try to change from MON to MMM

Re: Oracle SQL query works in SQL Developer but not QlikView

Hi,

Have you tried without to_date? Like

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

jaaldurgam
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

try this..


TIMESTAMP#('23-SEP-15','DD-MMM-YY HH:MMSmiley FrustratedS')

sasiparupudi1
Honored Contributor III

Re: Oracle SQL query works in SQL Developer but not QlikView

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
Valued Contributor

Re: Oracle SQL query works in SQL Developer but not QlikView

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