3

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?

Improve this question
3
  • 3
    What is the data type of the start_Time field? (VAR)CHAR or TIMESTAMP? Commented Oct 25, 2012 at 14:45
  • both begin and end date fields are datetime Commented Oct 28, 2012 at 23:25
  • @Jake - Firebird does not have a datetime data type. Commented Oct 29, 2012 at 15:44

4 Answers 4

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I do use DateTime fields, not strings. i added the second example in my post to show that if I change my params to AsString, i get the same error
I guess then the problem is your use of TRUNC in the query, as that only converts numbers, not dates. I guess the error might actually about the conversion in TRUNC, instead of the parameter. See firebirdsql.org/refdocs/langrefupd25-intfunc-trunc.html
It does seem to be the trunc causing the problem, so how do i get rid of the trunc function and select those records that have a true start_date >=:beginDateRange and <=:endDateRange. I am concerned with the time portion of the date. Will I truly get the coorect result if ParamByName('beginDateRange').AsDate:= Date - 30; and ParamByName('endDateRange').AsDate:= Date; ---thanx
Use CAST(field AS DATE) instead of TRUNC to get the date part of timestamp.
2

Firebird doesn't support conversion from string to date and time value if string is in 12 hour format. Use 'dd.mm.yyyy hh:mm:ss' or 'mm/dd/yyyy hh:mm:ss' formats.

Improve this answer

1 Comment

As I examplained to two others above, i do not use strings. Both my start and end fields are DateTime fields. My params are receiving the values asDateTimes (i even tried asDate). I only shoed the example asString to say that i get the same error
0

String to date/time conversions usually use user's locale. So if you feed a date/time conversion function with string that does not match your date part of the locale - you will get similar errors.

Also specifying date/time values like "10/25/2012" is Locale dependent. So if you execute your program on a computer with different than US Locale (like Mine) - it's likely to fail if using "10/25/2012".

To be Locale independent I suggest two options:

  • Use StrToDateTime, specifying TFormatSettings
  • Use ISO 8601 for specifying date/time strings (but I don't think Delphi supports that...)

BTW programs like MS Sql, Excel etc. accept dates in ISO 8601. But you have to check this for FB.

Regarding this:

...since my datetime fields are in the "10/25/2012 9:20:49 AM" format in the database...

The internal storage of date/time fields varies between different DB Engines. What you see in your DB Management Software ("10/25/2012 9:20:49 AM" in your case) is the string representation of the data field, usually formatted (again) according your user Locale

Improve this answer

4 Comments

The point of using AsDate is to let the DB driver handle the necessary conversion using its own native formats as needed so you don't have to. If AsDate is doing a bad string conversion then it is either a TParam bug or a DB driver bug.
I do not use strings, I use DateTime fields, not strings. i added the second example in my post to show that if I change my params to AsString, i get the same error.
@JakeSnake: So may be Remy Lebeau is right - may be it is DB driver bug? What components do you use for accessing the database?
@JakeSnake: Also does that problem occurs for all the three DB Engines you use or one of them?
0

if you connected with DB from Firebird 1.0 under the server Firebird 2.1 (for example) you need todo backup and restore under Firebird 2.1

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.