Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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

View solution in original post

7 Replies
Highlighted
Specialist
Specialist

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

Specialist
Specialist

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.

Highlighted
Specialist
Specialist

try to change from MON to MMM

Highlighted

Hi,

Have you tried without to_date? Like

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

Highlighted
Specialist
Specialist

try this..


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

Highlighted
Master III
Master III

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

Highlighted
Specialist
Specialist

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

View solution in original post