[Solved] SQL: Comparing DATE Field's Month To INTEGER param.

[Joe129]

Hi,

Having tried to use Google searches to find out what's wrong with my simple WHERE statement, I would like to just ask you users of this forum what's wrong with it:

Code: Select all

SELECT "Customer"."Name", "Address_Line1", "Address_Line2", "Job"."Job_Date", "Job"."Job_Time", "Job"."Hours_Worked"
FROM "Customer", "Job"
WHERE MONTH("Job"."Job_Date") = VALUE(:month);

The Job_Date field's type is DATE.

I assume the input prompt would provide the Integer parameter "Month" as Text, yet having the expression VALUE(:month) in this statement is causing my query to fail, while it works with this expression replaced by an Integer constant and this statement also failed when I replaced VALUE(:month) with just a reference to this parameter in case it was already an Integer value.

Error message:

SQL Status: HY000
Error code: 1000

Syntax error in SQL statement

[Villeroy]

MONTH("Date") returns an integer between 1 and 12
In this context (parsed SQL) there is no function named VALUE and it is not necessary. Any named parameter such as :month will be evaluated by the SQL parser that is built into Base.

Code: Select all

...
WHERE MONTH("Date") = :month

should simply work.

[Joe129]

Thanks for helping me. I had tried that WHERE statement before posting this question but I received the same syntax error message, I've just realized this is because month can't be used as a variable name because its a reserved name.