Re: Default NLS_DATE_FORMAT opinions please

This was my feeling about the issue, but I wanted to hear from others before I took a position. Basically, for any code that is to be used by others, specify date values using the to_date() function to guarantee your code will work.

I was inspired to post this message by one of the examples in the SQL training course. When I ran the example, I got the wrong answer because of a two digit date literal.

However, I'm changing my default date format to "MM/DD/YYYY" just for display purposes.

"Nick Pope" <nick.pope_at_retaininternational.com> wrote in message news:t8q2e92k49s144_at_xo.supernews.co.uk...
> I would suggest that in all code you explicitly tell Oracle what format
 you
> are using in SQL queries. This ensures that if you change the setting your
> code will still work!
>
> E.g.
>
> rather than
>
> select *
> from sales
> where sales_date > '01-Jan-2001'
>
> you would write
>
> select *
> from sales
> where sales_date > to_date('01012001','ddmmyyyy')
>
> i.e. I have explicitly told Oracle what format my data parameter is in.
>
> Why do this?
> - if you change the data format for the database
> - if you create copies of the database and forget to set all parameters to
> the same format you may hit this problem
> - if you create copies of the database for other countries, they may
 insist
> on using a different format and then your code will not work!
>
> Uchakra <uchakra_at_aol.com> wrote in message
> news:20010215233456.04907.00000247_at_ng-cd1.aol.com...
> > In general 'DD-MM-YYYY' should be OK.
> > Some applications may require
> > precise time to distinguis one row (date field) from
> > another and for that purpose, some people
> > use a format like
> > 'DD-MM-YYYY HH24:MI:SS'
> >
> > Think about your rquirement to see if
> > you need that.
> >
> > In most case 'DD-MM-YYYY' would be OK.
> >
> > Regards
> > Willy
>
>
Received on Fri Feb 16 2001 - 07:51:15 CST