Re: Strange Problem

[Vishner@AOL.COM]

Hi to all,

For the small population of us who are over 50 years
old, I object to the use of "-RR" as a long term solution. It is fine if no dates occuring before 1950 are processed, but if my birthday is inserted, the resultant year would be 2048 isntead of 1948.

Alternatively, use NLS_DATE FORMAT (or to_char mask
everywhere) containing a 4-digit year
(e.g.,'dd-mon-yyyy', 'yyyy-mm-dd'). I understand that this might mean more work if dates are parsed or reformatted, but it is safer).

If you are using a fairly recent release of SQR, you can modify the SQR default date format. If using an old version, you can change the NLS_DATE_FORMAT only for the duration of that SQR by coding the following in begin-SETUP/end-SETUP

begin-SQL
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'
end-SQL

This will ensure that any other applications will continue to see dates in standard ORACLE format.

HTH,
Hugh


In a message dated Wed, 5 Jan 2000  7:59:04 AM Eastern Standard Time, "Johnson, Dan" <Dan_Johnson@WRIGHTEXPRESS.COM> writes:

> Hi,
>         What DBMS are you using? If you are using Oracle here is what is
> happening. Your Oracle NLS date format is 'dd-mon-yy' this means that when
> you insert a date with a two digit year Oracle will attach the current
> century, which is now 20 as opposed to 19,  onto the front of the year.
> Hence you get 2099 instead of 1999. To correct this you can do one of two
> things. You can change your Oracle NLS date format to 'dd-mon-rr' which is
> the Oracle windowing format. This is the easiest and safest way to fix the
> problem. Or you can find all of your date inserts and use the Oracle date
> function to_date(&some_date,'dd-mon-rr') when you insert. I don't know about
> other DBMS systems but I am sure they have something similar that you can
> do.
>
> Dan
>
>         Hello All,
>              I have a strange problem. I have a SQL statement in SQR. I
> select a date
>         from a table .Take for instance the date is c_dt, the database
> variable is
>         referenced as &c_dt.
>
>         In the Insert statement in the same SQR , I'm inserting &c_dt into a
> date field.
>         The insert executes successfully. But surprisingly , a date
> value(say
>         01-jun-1999') is inserted as ('01-jun-2099').
>
>         Could anyone throw some light on this ?. And let me know what the
> problem is .
>         Even though &c_dt is not a date variable but a character variable ,
> it should
>         have populated it as 19yy instead of 20yy right ?.
>
>
>         Thanks in Advance.