Re: y2k

It's probably because your NLS_DATE_FORMAT is 'DD-MON-YY'. You can find the NLS_DATE_FORMAT (for the session) in the nls_session_parameters view.

SQL> SELECT * FROM nls_session_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   FRENCH
NLS_TERRITORY                  FRANCE
NLS_CURRENCY                   F
NLS_ISO_CURRENCY               FRANCE
NLS_NUMERIC_CHARACTERS         ,.
NLS_DATE_FORMAT                DD/MM/YY
NLS_DATE_LANGUAGE              FRENCH
NLS_SORT                       FRENCH
NLS_CALENDAR                   GREGORIAN

9 ligne(s) sélectionnée(s).

If you don't use the same date format than in your NLS_DATE_FORMAT, you must use TO_DATE with the format you want to specify.

For example :
INSERT INTO toto VALUES (TO_DATE('01-jan-2000','DD-MON-YYYY')); OR
INSERT INTO toto VALUES (TO_DATE('01012000','DDMMYYYY'));

You can also modify the NLS_DATE_FORMAT of the session before (and after if necessary)
inserting or updating rows but it can be dangerous. For example :
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; And now you can write :
INSERT INTO toto VALUES ('01-jan-2000'); ...
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY'; This way is sometimes useful when you use SQL in client side if you're not sure all
the PCs have the same parameters.
To have simpliest queries in the client application, I decided to alter the session
in a stored procedure (with Dynamic SQL) called by the client application at connect time.

-----Message d'origine-----
De : Patrick RIBAULT <Patrick.Ribault_at_dreamtel.fr> Groupes de discussion : comp.databases.oracle.server Date : dimanche 9 mai 1999 11:56
Objet : y2k

>What is the correct syntax to insert a date with oracle 7.3
>
>insert into toto values ('01-jan-00') works but isn't fine with y2k
>insert into toto values ('01-jan-2000') does NOT work : Not a valid date
>
>What must I do ?
>
>--
>Patrick RIBAULT
>
>
>

Patrick RIBAULT a écrit dans le message <37355a4d_at_nuage.dreamtel.fr>...
>What is the correct syntax to insert a date with oracle 7.3
>
>insert into toto values ('01-jan-00') works but isn't fine with y2k
>insert into toto values ('01-jan-2000') does NOT work : Not a valid date
>
>What must I do ?
>
>--
>Patrick RIBAULT
>
>
>
Received on Sun May 09 1999 - 13:01:06 CDT