At 09:05 AM 14-02-01 +0100, you wrote:

>At 14:54 14.02.2001 +1300, Johannes wrote:
> >Only dmy, mdy, ymd works for stored procedures, and worse only mdy, ymd
> >works for queries and cursors. That's no good. The user should be able
> >to pick any format they want!
>
>Well, the end user should be able to pick any format he/she wants. The
>application programmer, system administrator or anyone who writes SQL could
>be forced to use a particular format - at least in my opinion.
>
> >This looks to me as though there is some filtering being done here
> >implying restrictions as to what date format the user can select unless
> >the application performs an expensive date format analysis to provide in
> >turn the restricted date format for queries.
> >
> >What's wrong here?
> >Who is it?
> >Interbase or IBO?
> >Am I missing something?
>
>Once again I have to disagree with Helen (that's twice in about one week).
>I'd say your missing something, and not blame Interbase ;=) You're correct
>in that you cannot write something like
>
> IB_Query1.SQL:='SELECT * FROM TABLE1 WHERE SOMEDATE = '+Edit1.Text;
>
>for any date format, but
>
> IB_Query1.SQL:='SELECT * FROM TABLE1 WHERE SOMEDATE = :SOMEDATE';
> IB_Query1.Prepare;
> IB_Query1.ParamByName('SomeDate').AsDate:=strtodate(Edit1.Text);
>or
> IB_Query1.SQL:='SELECT * FROM TABLE1 WHERE SOMEDATE =
>'+strtodate(Edit1.Text);
>
>should work if your Date settings are properly defined in your Control
>Panel. Thus, it should be no problem to avoid any date format issues for
>your end users (that is, unless you allow them to type the entire SQL
>string themselves).

Well, Svein, you and I don't disagree at all. :))

I said:
"Sure! but you will have to intercept the input in order to get it into the format that InterBase wants for storage or for parameter inputs to stored procedures"

That's just what you said - and went on to describe how interbase can't use just any old string for a date.

I think you also don't quite understand what he meant "or am I missing something here?" That doesn't mean "Is something missing?" (because in Delphi it MOST CERTAINLY IS !!! and IB's date format support is abysmal...); it means "Is there something I should know that I don't know?" (There's only one language sillier than idiomatic English, and that's VB, which claims to be "English-like". <g>)

Delphi, along with Paradox and DBase, too, when Borland had it, is really mean about date formats. StrToDate works only on the US mm/dd/yy format so if one lives in the International community, one has to do all sorts of weird stuff with DecodeDate and EncodeDate to actually have your Delphi app recognise and convert other formats properly.

I certainly agree that, if your users have a certain date format set up in their control panel, Delphi should respect it. And it does - in output. For input, it doesn't. So one has to maintain a little utility library to handle stuff like, e.g. St Valentine's Day in Australian, which is 14/02/2001. Set your Windows time zone to Sydney, Australia and see what Delphi does with StrToDate('14/02/01') or StrToDateTime('14/02/01') ! Nobody in Australia writes the date 14.02.01 or 14-FEB-01 but nobody writes it 2/14/01 either...

I suggested to Johannes that he look into the MaskEdit property as, if it is responsive to non-US date formats, it might save him at least some utility code to enable his users to input dates in a preferred format. Once you arrive at something which Delphi accepts as a valid TDateTime, then ParamByName('MyDate').AsDateTime is the right way to go. Just be careful to test a good range of inputs. 9/2/01 looks like February 9 to me - and Delphi won't raise an error on it. However, when you StrToDateTime() it, it will be stored as September 2.

:))

Helen




All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________