Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

WHERE select with date format


I am having trouble getting my SELECT to work using a WHERE clause.

The format that Date/Time Occurred is coming into QV is in "YYYY-MM-DD HH:MM:SS.FFF"

LOAD "Date/Time Occurred",

date("Date/Time Occurred", 'YYYY-MM-DD') as "DATE_OCCURRED",

TRIM(YEAR("Date/Time Occurred")) as YEAR,


(MONTH("Date/Time Occurred")) as MONTH,


(DAY("Date/Time Occurred")) as DAY



FROM (table)

WHERE "Date/Time Occurred" > 1270080000;

the error is:

SQP Error: [ODBC Driver] Data Types are not appropriate for relational operation...

5 Replies
Creator III
Creator III


The error occured is due to the foramt problem in your date/occured column,i think there is a problem with the date format of your database

See your date format will be enclosed in single quotes by default but here it is numeric in your where statement

So first check whether the datatype of the column is similar to the value used in condition ,In case if they are not similar


first convert your date to number .


ex: Where '14/05/2010 10:15:20 ' > 127008000 the output will be nothing in this case

so convert the date to number

where To_Number('14/05/2010 10:15:20 ' ) > 1270080000

try this hope you will find the solution during the processssssssssss



Not applicable

The "Date/Time Occurred" field is coming into QV from an outside DB. It is in the format of M/D/YYYY h:mm:ss example of a date/time ocurred is :

1/3/2010 8:39:10 PM

The problem is trying to query between specific dates...ex Jan 1, 2010 to jan 30, 2010....

I have tried using epoch time, d/m/yyyy, timestamp format and all have returned the same error message.

examples of previously used WHERE..

WHERE "Date/Time Occurred" > '1/6/2010';

WHERE "Date/Time Occurred" > 1270080000;

WHERE "Date/Time Occurred" > '2010-01-01 0:00:00';

Ajay, where did the To_Number field come from in your above example?


Not applicable

If your loading "Date/Time Occurred" as "DATE_OCCURRED" don't you want to say select Where "DATE_OCCURRED" > '1/6/2010'; just a thought.

Not applicable

nope, i've tried that just seems wierd that i cannot select the datetime format any which way for this WHERE clause

Not applicable

I was connecting to the server using the wrong driver....a ODBC driver instead of an Oracle one, only 2 weeks into Qlikview. A co-worker pointed that out. Thanks for the suggestions though, greatly appreciated.