OraFAQ Forum: Server Administration » Insert date/time value into a DATE field (error ORA-01843: not a valid month)

Home » RDBMS Server » Server Administration » Insert date/time value into a DATE field (error ORA-01843: not a valid month)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Insert date/time value into a DATE field (error ORA-01843: not a valid month) [message #371942]

Tue, 26 December 2000 03:38

Elois
Messages: 2
Registered: December 2000

Junior Member

I'm trying to format the date beginning with year, mth, day, and time and insert into oracle7 table. I'm trying to use only digits for the date and time, removing any hephens or colons if possible.

However, I keep encountering the error ORA-01843: not a valid month.
Below are two of severals I tired using. Is there anything wrong in the followings?
What is the accepted date format for oracle7 ?

1) fldDateA="2000/12/26:13:10:03"
"TO_DATE('" & fldDateA & "', 'YYYY/MM/DD:HH24:MI:SS')"

2) fldDateB="2000-Dec-26 23:10:03"
"TO_DATE('" & fldDateB & "', 'YYYY-MON-DD HH24:MI:SS')"

Pls help. Thanks!

Report message to a moderator

Re: Insert date/time value into a DATE field (error ORA-01843: not a valid month) [message #371949 is a reply to message #371942]

Tue, 26 December 2000 13:51

Kathy
Messages: 10
Registered: October 2000

Junior Member

Enclose with single qoutes.And remove the qoutes around &fldDateA in To_Date function.
Otherwise it takes "&fldDateA" as a parameter instead of 2000/12/26:13:10:03.

Try this,
fldDateA := '2000/12/26:13:10:03';
TO_DATE(&fldDateA, 'YYYY/MM/DD:HH24:MI:SS')

Report message to a moderator

Re: Insert date/time value into a DATE field (error ORA-01843: not a valid month) [message #371987 is a reply to message #371949]

Mon, 01 January 2001 21:35

Elois
Messages: 2
Registered: December 2000

Junior Member

Thanks for the suggestions.

I've got no knowledge of registry, pls elaborate further on how should i check the NLS_DATE_FORMAT.

Thanks!

Report message to a moderator

Re: Insert date/time value into a DATE field (error ORA-01843: not a valid month) [message #371993 is a reply to message #371949]

Tue, 02 January 2001 03:56

Marcus Fernando
Messages: 34
Registered: September 2000

Member

Start --> Run--> Regedit.
HKEY_LOCAL_MACHINE --> SOFTWARE --> ORACLE.

You can also use the command alter session to make this works only in your session without make changes in the registry. I only don't know the command now, but it's possible. It's something like that: ALTER SESSION SET NLS_DATE_FORMAT "DD/MM/YYYY" ...

Report message to a moderator

Previous Topic:	Info Related To DML to XML
Next Topic:	duplicate records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 20:42:08 CDT 2024