Re: Date datatype

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Mon, 29 Nov 1999 13:41:32 +0200
Message-ID: <81top5$6b8$1@ctb-nnrp1.saix.net>

Chi Sim Tang wrote in message <81thto$190$1_at_mawar.singnet.com.sg>...

>I am using ASP scripts to insert records via ODBC from the SQL database to
>the Oracle database. I have a DATE column in a table but it kept saying
>error not a valid month. Specifically, I need to insert datetime records of
>the format MM/DD/YY HH:MM:SS AM eg. "11/29/99 5:31:25 PM". Oracle, even in
>SQLPlus, does not accept this.
>
>Am I missing anything?

The TO_DATE function or the NLS_DATE_FORMAT.

By default Oracle's date format is DD-MON-YY I think. If you want to insert a different format, you either need to use the TO_DATE conversion function or use an ALTER SESSION SET NLS_DATE_FORMAT command to change the default format for that session. Of course, you can also change the INITsid.ORA file and specify a new default format for that instance, but I would rather leave that option alone.

TO_DATE should be the easiest in your case I think, e.g. INSERT INTO foo
( mydate )
VALUES
( TO_DATE('11/29/99 5:31:25 PM', 'MM/DD/YY HH:MI:SS AM') ) regards,
Billy Received on Mon Nov 29 1999 - 05:41:32 CST