where clause in SQL statement

[Anonymous]

Here is my load statement

LOAD  eventid,

    itemid,

    soldon,

    Year(soldon) as Year,

    Month(soldon) as Month,

    Day(soldon) as Day,

    price;

SQL SELECT *

FROM advast

where soldon>'11/11/2014';

I get an error saying

SQL SELECT *

FROM advast

where soldon>'11/11/2014'

I'm not sure what is wrong with my script, without the WHERE clause it works fine.

Does the where clause just not work in a SQL statement in QV?

[#2]

Try this:

WHERE

date(soldon) > date(11/11/2014)

[Anonymous]

i tried it, it didn't work

[pljsoftware]

Hi Smaranda,

what is your DB?

If you run the script in debug you can see the exact query executed, you can copy and past this query in your SQL Tool and execute it.

You have to check the correct sintax for the query and try to create in the script a similar query.

Usually for the date in query I like to write date in format YYYY-MM-DD.

Regards

Luca Jonathan Panetta

PLJ Software

[#5]

ok. If I were you I would try to narrow down the issue.

1) Does this work?

WHERE date(soldon) = today() ;

2) On your load statement try both soldon, date(soldon) and see what date format you get without the where clause. This is to see how date() function converts soldon field. Is that a true date or string field?

3) Depending on the result of no. 2 above, try using similar date values in your WHERE clause and see what happens.

It should be that difficult but think something is missing as I tried the no. 1 step and worked for me. But of course, i'm using DB2 and SQL server databases.

[rustyfishbones]

Try changing the Date around

LOAD  eventid,

    itemid,

    soldon,

    Year(soldon) as Year,

    Month(soldon) as Month,

    Day(soldon) as Day,

    price;

SQL SELECT *

FROM advast

where soldon > '2014/11/11';

[MarcoWedel · Accepted solution]

LOAD  eventid,

    itemid,

    soldon,

    Year(soldon) as Year,

    Month(soldon) as Month,

    Day(soldon) as Day,

    price

where soldon>'11/11/2014';

SQL SELECT *

FROM advast;

should work but could take a longer time to load.

[anbu1984]

Which database? Are you getting error while connecting to database? Or Are you not getting correct results?

[Anonymous]

I tried a different ways of querying

soldon>'11/11/2014'

soldon>'2014/11/11'

date(soldon)>date(11/11/2014)

date(soldon)>today()

I confirmed the format is MM/DD/YYYY

the only way it worked so far was this

LOAD  eventid,

    itemid,

    soldon,

    Year(soldon) as Year,

    Month(soldon) as Month,

    Day(soldon) as Day,

    price

where soldon>'11/11/2014';

SQL SELECT *

FROM advast;

[JonnyPoole]

This is a guess without having a visual foxpro database to test, but this link provided some ideas....

Visual FoxPro ODBC Driver query fails if the date format is not the AMERICAN date format

You may also need to pass in the data as a variable

IE:

Set  vDate='{07/22/93}';

And then...

Select * FROM ORDERS WHERE ORDER_DATE <  $(vDate)

[Anonymous]

This work but as you said it's slow and I'm worried it might slow down the database