At 06:45 AM 1/04/2004 +0100, you wrote:

>Hello All,
> While inserting timestamp(or may be date) in a table, the format I've
> to stuck to is 'mm/dd/yyyy hh:mm:ss' (or 'mm/dd/yyyy' in case of date
> field). Is there any other way I can change the format while inserting
> timestamp in a table? Has local regional settings something to do with
> this format?

As far as the database engine is concerned, no. For literals in the SQL
that is submitted use

'mm/dd/yyyy'
or
'dd.mm.yyyy'
or
'dd-MMM-yyyy'
or
'yyyymmdd'

(There are other available formats but 'dd/mm/yyyy' isn't one of them).

> I'd a stored procedure just inserting timestamp data in a table. I
> passed the data in above mentioned format. It was working fine on few
> machines but on a particular machine the stored procedure was not
> working. It just hung. Just with a hit, I changed the local settings, and
> it worked fine. I tried with other machines as well and found the same
> behaviuor.
>Just wanted to confirm with this behaviour.

The database itself doesn't take any account of the locale setting, but
most platform-specific application interfaces do. What they pass in the
SQL is the data in its native date type format, i.e. they don't use the
date literal conventions at all. You need to be concerned about the date
literal format only if your application is composing date literals, or is
taking user input "as-is" and treating it as a date literal.

You will get "apparent success", for example, passing '01/04/2004' as a
date literal. You think it means "April 1 2004" but the engine stores it
as "January 4 2004". At this time of the year, you will be exception-free
until the 13th of the month. However, all of the date literals stored so
far for the month (except 1/01, 2/02, 3/03, etc.) will have been stored as
wrong dates.

/heLen