Date in where clause does not works

[#1]

I am using this in my SQL script

From employees where firstname = 'Paul' and Submit_Date = '1/1/2011'

My query runs fine without the Submit_Date clause

Why doesnt my script run? I will appreciate a response. I checked if my date is being interpreted correctly and when I use the list box properties of Submit_date and select number I see values there which I believe are correct.

[#2]

Sorry for the confusion but I matched the fields and still the same error. My Submit_Date comes out in the format as in the example below

3/4/2009 2:00:34 PM        

Do I have to modify the TO_DATE function?

[MayilVahanan]

Hi

Yes, please modify according to your date format in TO_DATE function.

Hope that helps

[#4]

Still the same error. Tried every possible format.

[Anonymous]

Can you show here the whole SQL query?

[#6]

Below is the query

LOAD  First_Name,Submit_Date,Year(Submit_Date) as Year, Month(Submit_Date) as Month, Day(Submit_Date) as Day;

SQL SELECT

    First_Name,Submit_Date

From employees where First_Name = 'Paul' and

Submit_Date = TO_DATE('01/01/2012', 'MM/DD/YYYY HH:mm:SS');

[Anonymous]

Looks OK, hard to guess what's wrong without actual trying.  I still suspect data format...

The easy workaround, since you use the preceeding load anyway, remove the date condition from the Oracle part and add it to QV part:

LOAD  First_Name,Submit_Date,Year(Submit_Date) as Year, Month(Submit_Date) as Month, Day(Submit_Date) as Day

WHERE Submit_Date = '1/1/2012';            // or is it 2011?

SQL SELECT

    First_Name,Submit_Date

From employees where First_Name = 'Paul';

[#8]

mhm that a very good workaround. I actually dint knew you could use a where clause in the Load as well. So whats the difference really between using where in Load and where in SQL part?

[Anonymous]

From the end result, there is no difference.  I think there is difference in performance - using "WHERE" in "SQL SELECT" should be faster than in "LOAD".  If your data set is not too big, you're not going to see the difference.

[#10]

Yeah thats what I thought. My data is big though so still might have to figure out the original problem but thanks a lot for your help.