Date Syntax in a Where clause

[tjn006]

Hello!

I am developing a query which is filtering a Date field. I want to select only records which have a certain promise date.

Here is my query:

SELECT T1.order_id,T1.Lastname,T1.firstname,T1.b_city,T1.s_city,T1.s_state,T1.Total,T1.status,
T2.SchoolCode,T2.Entrydate,T2.ShipDate, T2.PromiseDate
FROM cscart_orders T1 LEFT JOIN HSD_OrderHeader AS T2
ON T1.order_id=T2.OrderNo
ORDER BY T1.order_id
WHERE T2.PromiseDate='11/05/2012';

When I run the query, I get a syntax error for the Where clause.

What am I missing?

Also, when it comes to entering the date, can I use a variable that comes from outside the Query? How would I do this?

Thanks in advance!

Tom Nesler

[AndreyZ]

If you want to use date values in the string format, you should use the format that MySQL accepts. For example:

Code: Select all

WHERE T2.PromiseDate='2012-05-11'

For more information, please read the following articles:
http://dev.mysql.com/doc/refman/5.0/en/ ... erals.html
http://dev.mysql.com/doc/refman/5.0/en/ ... types.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Also, you can use parameters in your queries instead of constant string values. Here is a code example:

Code: Select all

MyQuery1.SQL.Text := 'select * from testdates where dt=:dt'; // the dt field has the DATE type on the server
MyQuery1.ParamByName('dt').AsDate := Now;
MyQuery1.Open;