Re: Insert date into Oracle

On 24 May 2004 06:06:10 -0700, dragon19993_at_yahoo.com (Allan) wrote:

>I did change the NLS_DATE format and tried to run the sql code:
>
>insert into AD.aracct_range (acct_range, acct_range_desc, login,
>log_date, log_time)
>values ('b', 'not applicable', 'wang', '05/21/2004', '14:24');
>
>Oracle still gives me error message indicate that I can't insert
>'05/21/2004' into date column.
>
>Does anyone has any idear how to get this problem around.
>
>Thanks in advance
>
>Alan
>

Alan,

what is the data type for the log_time column ? If it is a DATE then you need TO_DATE on that as well - Oracle doesn't have TIME only data types yet.

If it's a VARCHAR2 and you will eventually be doing DATE arithmetic on it, you are going to be severely b*gg*red by a porcupine - because it will all go to hell. I know, I've just spend some days convincing a supplier that storing dates in a VARCHAR2 column was asking for trouble - they didn't think so, so I trashed their table(s) to demonstrate. We're getting new code soon :o)

On the other hand, your session should have looked like this :

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY'; INSERT INTO AD.ARACCT_RANGE(...) VALUES (........ '05/21/2004', ...); If so, you should have had no problems.

If you did have problems, cut and paste the output etc from SQL*Plus into your next message along with error messages and codes, Oracle version numbers, your inside leg measurement (only kidding) etc.

Cheers,
Norman.

Peeking round the firewall.

Remove the obvious bit to reply to my email address. Received on Mon May 24 2004 - 11:18:10 CDT