I've received a request for NLS_DATE_FORMAT to be set globally in a
database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
use the time don't have to use the to_date function.

Is this a reasonable or common thing to do? We're in a single
timezone in a single country, so there are no localization
issues to worry about.

TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

On Thu, 21 Jun 2007 19:03:33 GMT, Mark Harrison <mh@pixar.com> wrote:

>I've received a request for NLS_DATE_FORMAT to be set globally in a
>database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
>use the time don't have to use the to_date function.
>
>Is this a reasonable or common thing to do? We're in a single
>timezone in a single country, so there are no localization
>issues to worry about.
>
>TIA!
>Mark

It is a thing that won't work, as NLS setting are always overridden by
the client. If the client didn't set them they are overridden to the
defaults of the particular Oracle port.

--
Sybrand Bakker
Senior Oracle DBA

Mark Harrison <mh@pixar.com> wrote in news:9Yzei.2715$vi5.1180
@newssvr17.news.prodigy.net:

> I've received a request for NLS_DATE_FORMAT to be set globally in a
> database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
> use the time don't have to use the to_date function.

HUH?

> don't have to use the to_date function.

TO_DATE is used to convert strings to date datatype!

Are you sure you even know how to spell S-Q-L?

Ana C. Dent <anacedent@hotmail.com> wrote:
> HUH?
>
> Are you sure you even know how to spell S-Q-L?

Idiot. The question was very reasonable. Go away.

Laurenz Albe

On Jun 21, 9:03 pm, Mark Harrison <m...@pixar.com> wrote:
> I've received a request for NLS_DATE_FORMAT to be set globally in a
> database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
> use the time don't have to use the to_date function.
>
> Is this a reasonable or common thing to do? We're in a single
> timezone in a single country, so there are no localization
> issues to worry about.
>
> TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

What clients are used to run the ad-hoc queries?
Maybe you can find a way so that all the clients have some default
date format. (e.g. login.sql with sqlPlus)

br,
Martin

sybrandb@hccnet.nl wrote:
>>I've received a request for NLS_DATE_FORMAT to be set globally in a
>>database to "Mon dd yyyy hh:mi:ssAM" so that ad hoc queries that
>>use the time don't have to use the to_date function.
>>
>>Is this a reasonable or common thing to do? We're in a single
>>timezone in a single country, so there are no localization
>>issues to worry about.
>
> It is a thing that won't work, as NLS setting are always overridden by
> the client. If the client didn't set them they are overridden to the
> defaults of the particular Oracle port.

To go into more detail:

See http://download-uk.oracle.com/docs/c...v.htm#g1012703

If no environment variable/registry setting is set on the client,
the setting will be DD-MON-RR.

If NLS_LANG is set and NLS_DATE_FORMAT is not set, the format will be
derived from the territory part of NLS_LANG.

If NLS_DATE_FORMAT is set, this will be respected.

Additionally, you can use ALTER SESSION to set NLS_TERRITORY or
NLS_DATE_FORMAT for the session.

Here is a (slightly ugly) solution for the problem:

You can create a login trigger for the user that will change the
setting of NLS_DATE_FORMAT for the session.

Yours,
Laurenz Albe