Home » SQL & PL/SQL » SQL & PL/SQL » In trouble with the date format
In trouble with the date format [message #204847] Wed, 22 November 2006 06:34 Go to next message
brandy
Messages: 2
Registered: November 2006
Location: Hannover / Germany
Junior Member
i wan't to simply update a date column in a oracle database
belonging to a baan erp system.
on all colums baan has the contraint "not null".
if you use the baan interface to put an empty date field into a oracle table somthing strange happend.

a select on that column brings the following result:

select date from tccom020100;

date
--------
01.01.99

if i use to_char to format the date column:

select to_date(date, 'dd.mm.yy hh24:mi:ss');

to_char(date)
--------------
00.00.00 00:00:00

the problem is, is it not possible to put "00.00.00" into a oracle date column. the to_date function leaves with an error:

ORA 01847: day of month must between 1 and lastday of months

regards
Andreas


Re: In trouble with the date format [message #204872 is a reply to message #204847] Wed, 22 November 2006 07:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Date must either have a valid format (existing day, month and year), or must be NULL. As there's no "0th day" of "0'th month", this value is invalid and thus the error you received.
Re: In trouble with the date format [message #204873 is a reply to message #204847] Wed, 22 November 2006 07:50 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
1. If the column has a NOT NULL constraint, you cannot put and "empty" value. that is the purpose of a NOT NULL constraint. Is that not clear to you?

2. You don't use TO_DATE on DATE columns, you use TO_CHAR.
Re: In trouble with the date format [message #204888 is a reply to message #204873] Wed, 22 November 2006 09:07 Go to previous messageGo to next message
brandy
Messages: 2
Registered: November 2006
Location: Hannover / Germany
Junior Member
hi,

i know i have to use to_char - it was a error here in this post.
i now also that i can't put NULL into a column with a constraint not NULL.
But 0 is not NULL!!!!
An why can the Baan software put those strange things in the date columns?

regards
Andreas
Re: In trouble with the date format [message #204890 is a reply to message #204888] Wed, 22 November 2006 09:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can guarantee that Baan has not managed to put an invalid date in an Oracle Date type.

Can you identify one of the rows that has one of these Baan dates in it, and run a query based on this:
SELECT to_char(<dodgy baan date>,'dd-mon-yyyy hh24:mi:ss' 
FROM <baan table>
WHERE <stuff to identify a single row>

This will let us see what they've actually put into the Db.
Re: In trouble with the date format [message #204893 is a reply to message #204847] Wed, 22 November 2006 09:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
select date from tccom020100;

date
--------
01.01.99

if i use to_char to format the date column:

select to_date(date, 'dd.mm.yy hh24:mi:ss');

to_char(date)
--------------
00.00.00 00:00:00

Why go over all the trouble to fake some sqlplus copy-paste and not just really copy-paste these two statements and results from sqlplus??
Re: In trouble with the date format [message #204897 is a reply to message #204893] Wed, 22 November 2006 09:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Info from ItToolbox
Quote:
By default the null date in Baan gets converted into Jan 1, 9999 B.C. and maximum date allowed is 12/31/9999AD.
But if $BSE/lib/defaults/db_resource file has the ora_date:1 set in it then the null date in Baan gets converted into Jan 1, 4712 BC.
Also if ora_date:1 is set then while reading from database both 01/01/9999BC and 01/01/4712BC get converted into NULL date.
and maximum allowed date will be 12/31/4712AD.

Previous Topic: with out union
Next Topic: calling a procedure within unix shell script
Goto Forum:
  


Current Time: Thu Dec 08 23:55:57 CST 2016

Total time taken to generate the page: 0.05486 seconds