Re: date insert for oracle 7.3.4 onwards and ms sql server

"Jeff Kish" <jeff.kish_at_ait-mmii.com> wrote in message news:4s39st0eual1pgtl4158kadhasaiag96tq_at_4ax.com...
>
> Greetings.
>
> I am trying to figure out a way to insert a date with a 4 letter year
> that is acceptable to Oracle 7.3.4 onwards as well as MS SQL Server.
> The below works on all except Oracle 7.3.4. Any suggestions?
>
> Thanks
> Jeff
>
> INSERT INTO mm2user (id,pswd,name,dept,sec_lvl,mod_freq,exp_date)
> values ('LOGOUT','B8BBB3BBA1A0','17-Dec-2036');
>
> Oracle 7.3.4 client results in sql*plus:
>
> ERROR at line 1:
> ORA-01830: date format picture ends before converting entire input
> string
>

Of course
The default NLS_DATE_FORMAT is DD-MON-YY (where YY is not Y2K compatible)
either change NLS_DATE_FORMAT in the registry or use (as you should *never ever* rely on default date formats this is the best solution) an explicit to_date(17-Dec-2036','DD-MON-YYYY') If you don't want that you could of course also issue alter session set nls_date_format = ...
before any insert.

Hth,

Sybrand Bakker,
Senior Oracle DBA Received on Wed Oct 10 2001 - 15:43:31 CDT