Re: y2k column datatype problem

What's happening is that Oracle is appended the current century to the two-character year that you supply; in other words, Oracle is looking for records where date_column greater than January 1, 2097. I saw this very issue at work on Friday.

The NLS_DATE_FORMAT should be 'DD-MON-RR' rather than 'DD-MON-YY'. There is a parameter in the init.ora file that controls the date format. I don't know the parameter name offhand, but I'm sure some savvy Oracle guru can fill in that detail.

rpereira_at_my-deja.com wrote:
>
> Hi gurus,
>
> Does anyboby knows why the query
> "select * from table_name where date_column > '01-jan-97'"
> returns "no rows selected"
> and the query
> "select * from table_name where date_column >
> to_date('01/01/1997','dd/mm/yyyy')"
> returns several lines ?
>
> No alter session was issued.
> In the props$ view the parameters are:
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_DATE_FORMAT DD-MON-YY
> NLS_DATE_LANGUAGE AMERICAN
> NLS_CHARACTERSET WE8ISO8859P1
> NLS_SORT BINARY
> NLS_CALENDAR GREGORIAN
> NLS_RDBMS_VERSION 7.2.3.0.0
> In the v$parameter view the parameters are:
> nls_language AMERICAN
> nls_territory AMERICA
> nls_sort
> nls_date_language
> nls_date_format 'DD-MON-YY'
> nls_currency
> nls_numeric_characters
> nls_iso_currency
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Jan 15 2000 - 20:05:10 CST