WHERE select with date format

[#1]

Hi,

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,

TRIM

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

TRIM

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

...

SQL SELECT *

FROM (table)

WHERE "Date/Time Occurred" > 1270080000;

the error is:

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

[spsrk_84]

Hi,

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

then

first convert your date to number .

i.e

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

Regards,

Ajay

[#3]

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?

-T

[#4]

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.

[#5]

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

[#6]

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.