Subject Re: Newbie: Setting the Date format
Author cdb4w
Hi Tim,

Ok, to fill you in with some of the details.....

I have designed my various applications such that they have no idea
what type of database is being used as the datastore, it could be
ACT!/Foxpro, Paradox for example and now I am adding Firebird.

All database communication is done in a single DLL written in plain
vanilla 'C' and all data regardless of type is communicated to this
layer as strings (thus avoiding problems with languages that do not
support pointers).

This middleware builds an enquiry (which for firebird is an SQL
statement) based upon these string values and executes it. Now
because in Australia generally all input screens capture the date in
DD/MM/YYYY format - that is what is being passed through.

So you can see my problem, at the time of query execution I do not
know which fields are dates and which are not. This makes conversion
difficult. Given that I also support other databases such as Paradox
which *does* accept DD/MM/YYYY in QBE and Local SQL, doing the
conversion at the application side would then require introducing
knowledge about the actual datastore - a no go!

I could check for Data in the format dd/mm/yyyy and replace with but what if an Alphafield happens to contain a date?
Really the problem is that firebird is not being flexible enough to
handle what is been a valid date format in the *real* world. I mean is hardly if ever used. Even dd-mm-yyyy would be a better

Given that we already allow Set Dialect = 3 or similar at the
datebase level, why can't we have Set SQLDATEFORMAT = 'DD/MM/YYYY'?

Of course, this changes nothing right now so I will have to work out
how I am going to handle this issue.

--- In, Tim Ledgerwood <tim@s...>
> I don't know what OS you are using, but in windows, whatever is
> (as opposed to whatever is stored) depends on your regional
> I understand that (again, however it is actually stored), FB
> several different date formats. In my case, I am using Delphi as a
> development tool for the client side GUIs, so what I display to the
user is
> not related to what is stored in the database.
> In order to force a certain date format before I post to the DB, I
> SetShortDateFormat or a similar procedure; but I find that I seldom
have to
> worry about that. If I have to pass an SQL string containing a date
to some
> query object, then generally I will build the string, making sure
that the
> date is in the format 'DD-MMM-YYYY' (e.g., '09-OCT-2003') which I
> found is accepted by all SQL servers that I have used.
> All my applications use DD/MM/YYYY format.
> Hope this helps,
> Regards
> Tim