Date Problem... 1

[Oppenhiemer]

Hi..

I seem to be having odd recults when working with dates. I have a table with a field called "DATEENTERED" which is defined as a TimeStamp data type. When editing data, it accepts date values using the UK format, as in dd/mm/yyyy. But, when it somes to querying the date, if I try to use the same format - I get a conversion error.

So the following SQL statement :

select ORDER_NO,DATEENTERED FROM MEDIA_ORDERS WHERE DATEENTERED = '14/04/2003';

Generates the error :

Conversion error from string "14/04/2003"

However, if I change the format to read..

select ORDER_NO,DATEENTERED FROM MEDIA_ORDERS WHERE DATEENTERED = '04/14/2003';

There is no problem (no conversion error generated) just no result are shown. As you would expect, given that the only record on the table has the value 14/04/2003!

I have even tried casting the value, such as :

select ORDER_NO FROM MEDIA_ORDERS WHERE DATEENTERED = CAST ('14/04/2003' AS DATE);

But this only generates the same error. This problem is really driving me mad! Any help would be much appreciated.. I will add that I am using Borland's OpenSource version 1.0.0.326 and Windows XP (which has been configured correctly to use the UK date format.)


Many thanks..

Opp.

 

[Otto]

The timestamp has 'time' part. If you set it anything else that 00:00 at insert, you have to define it in the select or cast the DATEENTERED.

select ... where cast(DATEENTERED as date)='04/14/2003'

The date format is very complicated . Use parametrized query instead of date string.

Otto

 

[Oppenhiemer]

Hi Otto..
Thanks for your feedback. I tried the cast as you expressed it. It did actually work, strange though it seems to want to work only with the US Date format ?

Anyway, I was actually using a paramatised query originally - but went to a simple "select" statement in order to try debug the problem. In fact, I am using Delphi to interface with Interbase. So Im using a paramatised TQuery component. Guess I will have to persevere in order to get my app working. Oh well.. back to the drawing board I guess ;-)

Cheers..

Opp.

 

[Oppenhiemer]

Ureka!

Or should i say.. "what the ...." Still not sure what the real problem was. But It seems to have been a problem with Delphi rather than Interbase.

Seems that once you make changes to the Interbase tables, some Delphi components can mis-behave. Created identical components from scratch to access table and voila.. they mysteriously work! Very annoying.

Still think the Date format is weird using IBConsole to query it. Will let you enter Date values in UK format.. but only query them using the Cast operator in the US format ??

Thanks again Otto for your response.

Cheers..

Opp.