Problem in conditional SELECT?

[#1]

Hello Everyone,

I hope it's a very basic questions, but as I recently started working on Qlikview, i don't know how to write the correct syntax for this. I've connected through a big data table, but I don't want to select the whole table as it has 11 million record so wanted to know how can we give the conditional select statement statement?

Oracle connection string;

SQL SELECT *

FROM table1

Where Snapshot_Date> '9/28/2010';

I added this where clause but I am getting an error which says :

ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: ORA-01843: not a valid month

SQL SELECT *

FROM Table1

WHERE SNAPSHOT_DATE >= '09/8/2009'

the snapshot date is a field in table1.

Thanks. I will appreciate your replies.

[rwunderlich]

I believe the correct date format for Oracle is:

WHERE SNAPSHOT_DATE >= DATE 'YYYY-MM-DD'

[#3]

Hi Rob,

thanks for replying. but the format is MM/DD/YYYY 5:23:06 AM...i.e there is an additonal time constraint in it.

Yaman

[rwunderlich]

Then use:

TO_DATE('09-08-2008 05:23:96 AM', 'MM-DD-YYY hh:mm:ss mi')

See http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm for precise syntax.

-Rob

[#5]

Thanks. so after that how should I write this in where clause? Is it something like:

SQL SELECT *

FROM SYSADM.DW_EMPLOYEES_VW

WHERE TO_DATE('SNAPSHOT_DATE', 'MM-DD-YYY hh:mm:ss mi') > '09-08-2008 05:23:96 AM';

I used the above statement and stillg ot the error. Sorry but i still didn't get it.

[rwunderlich]

WHERE SNAPSHOT_DATE >= TO_DATE('09-08-2008 05:23:96 AM', 'MM-DD-YYY hh:mm:ss mi')

-Rob

[#7]

thanks for replying.

SQL SELECT *

FROM table 1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY hh:mm:ss mi');

I used the above code and got the folllowing error:

ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: ORA-01810: format code appears twice

SQL SELECT *

FROM table1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY hh:mm:ss mi')

[danielrozental]

try this

SQL SELECT *

FROM table 1

WHERE SNAPSHOT_DATE >= TO_DATE('09/08/2009 05:23:09 AM', 'MM-DD-YYY HH:MI:SS PM');

[#9]

Hello,

It's still giving me the same error. Can you suggest something else?

Thanks.

[danielrozental]

Just read about oracle's to_date funciton and keep testing until you get the right one.

http://www.techonthenet.com/oracle/functions/to_date.php