conversion error from string, when using params in SQL

Question

using Delphi 2010 (Firebird [testing], MS Sql Server, Oracle [production])

The following is my SQL

SELECT p.script_no, MIN(p.start_Time) as startTime, MAX(p.end_Time) as endTime, 
SUM(p.duration) as TotalDuration
FROM phase_times p
WHERE (p.script_no=:scriptNo) AND (Trunc(p.start_time) >= :beginDateRange) AND (Trunc(p.start_time) <= :endDateRange) 
GROUP BY p.script_no



ParamByName('beginDateRange').AsDate:= Date - 30;
ParamByName('endDateRange').AsDate:= Date;

I am getting a "conversion error from string - 10-25-2012" and i am not sure why, since my datetime fields are in the "10/25/2012 9:20:49 AM" format in the database.

If i change it to the following : ParamByName('beginDateRange').AsString := formatDateTime('mm/dd/yyyy',Date - 30).....i get the error "conversion error from string - 10/25/2012"

reserching this error has provided me no new avenues, do you have any ideas?

Answer 1

According to the Interbase 6.0 manual, Embedded SQL guide, chapter 7, Firebird supports conversion from YYYY-MM-DD and YYYY-MM-DD HH:MM:SS.qqq. I also believe it supports American style shorthand dates (eg 1-JAN-2012) for conversion.

It may be there are some locale dependent conversion supported, but in general: use an actual date/timestamp type instead of a string.

UPDATE I initially did not spot the TRUNC in your query: it is the cause of the conversion error as this function only works on numbers, see the manual entry for TRUNC.

Given your comment (and the respons of ain) I assume you are only interested in the date part, and want to ignore the time. If so, rewrite your use of TRUNC to:

CAST(<your-timestamp-field> AS DATE)

And the condition (Trunc(p.start_time) >= :beginDateRange) AND (Trunc(p.start_time) <= :endDateRange) to:

CAST(p.start_time AS DATE) BETWEEN :beginDateRange AND :endDateRange