topic WHERE select with date format in QlikView

WHERE select with date format

Thu, 13 May 2010 20:58:04 GMT

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...

WHERE select with date format

spsrk_84 — Fri, 14 May 2010 05:07:34 GMT

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

WHERE select with date format

Fri, 14 May 2010 14:15:17 GMT

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

WHERE select with date format

Fri, 14 May 2010 19:49:17 GMT

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.

WHERE select with date format

Mon, 17 May 2010 13:02:52 GMT

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

WHERE select with date format

Mon, 17 May 2010 16:27:03 GMT

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.