|
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612202 is a reply to message #612190] |
Mon, 14 April 2014 07:14 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
ramadurga wrote on Mon, 14 April 2014 16:17how can i insert 00-jan-2014 into a date column?
Are you sure you want anything like that? No system/application would ever need such a thing. It's an invalid value. And you actually never store date in that format in DB. It's for humans to interpret the format. Internally it is stored in Oracle's format.
People usually get confused between STORING and DISPLAYING date values. I wonder if you really have such a requirement and want to display the date value as '00-jan-2014'.
SQL> SELECT '00-'||to_char(SYSDATE, 'mon-yyyy') INVALID_DATE FROM dual;
INVALID_DATE
------------
00-apr-2014
Have a look at this article by Ed Stevens : http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
|
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612204 is a reply to message #612199] |
Mon, 14 April 2014 07:16 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
ramadurga wrote on Mon, 14 April 2014 17:36Actually...In PRODUCTION when we are taking backup to a backup table from a main table it is inserting some values like '00-xx-xxxx'.
And that's IMPOSSIBLE if the data type is DATE. Please post the table DDL. It has to be a string type and that's the root cause.
|
|
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612207 is a reply to message #612199] |
Mon, 14 April 2014 07:23 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
ramadurga wrote on Mon, 14 April 2014 08:06Actually...In PRODUCTION when we are taking backup to a backup table from a main table it is inserting some values like '00-xx-xxxx'.
Then I suggest to check application that is populating your production table. One way or another all code boils down to OCI calls. And it supports both external and internal datatypes. Normally we operate using external datatypes and Oracle converts them to internal datatypes. That's when validation occurs. However, for performance reasons Oracle allows declaring variables of internal datatype. Then no conversion and therefore no validation occurs. As a result you can come up with dates like 0th day of 57th month.
Another possibility is data block corruption.
SY.
|
|
|
|
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612214 is a reply to message #612208] |
Mon, 14 April 2014 08:11 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Indu_Bandham wrote on Mon, 14 April 2014 08:25for a date type, its not at all possible to get this 00-jan-2014 value. impossible either to insert or update or to display.
Again, it is possible to insert anything into a data column if using internal date datatype. You can do this using OCI, or any PRO* languages. You can even do it in SQL*Plus using package procedure dbms_stats.convert_raw_value which uses internal datatypes (assuming you follow Oracle internal datatype structure):
SQL> create or replace
2 function bad_date
3 return date
4 is
5 v_date date;
6 begin
7 dbms_stats.convert_raw_value('64640000010101',v_date);
8 return v_date;
9 end;
10 /
Function created.
SQL> select to_char(bad_date,'dd-mon-yyyy') dt
2 from dual
3 /
DT
-----------
00-000-0000
SQL>
SY.
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612215 is a reply to message #612214] |
Mon, 14 April 2014 08:19 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can put anything in the date but you will then always retrieve date 0:
SQL> create or replace function bad_date return date
2 is
3 v_date date;
4 begin
5 dbms_stats.convert_raw_value('00000000000000',v_date);
6 return v_date;
7 end;
8 /
Function created.
SQL> select to_char(bad_date,'DD/MM/YYYY HH24:MI:SS') from dual;
TO_CHAR(BAD_DATE,'D
-------------------
00/00/0000 00:00:00
1 row selected.
SQL> create or replace function bad_date return date
2 is
3 v_date date;
4 begin
5 dbms_stats.convert_raw_value('11111111111111',v_date);
6 return v_date;
7 end;
8 /
Function created.
SQL> select to_char(bad_date,'DD/MM/YYYY HH24:MI:SS') from dual;
TO_CHAR(BAD_DATE,'D
-------------------
00/00/0000 00:00:00
1 row selected.
SQL> create or replace function bad_date return date
2 is
3 v_date date;
4 begin
5 dbms_stats.convert_raw_value('12345678901234',v_date);
6 return v_date;
7 end;
8 /
Function created.
SQL> select to_char(bad_date,'DD/MM/YYYY HH24:MI:SS') from dual;
TO_CHAR(BAD_DATE,'D
-------------------
00/00/0000 00:00:00
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612216 is a reply to message #612215] |
Mon, 14 April 2014 08:44 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 14 April 2014 09:19
You can put anything in the date but you will then always retrieve date 0:
Again, you need to follow internal date format. For example, OP has 00-jan-2014:
create or replace
function any_date_you_want(
p_str varchar2
)
return date
is
v_date date;
begin
dbms_stats.convert_raw_value(p_str,v_date);
return v_date;
end;
/
Function created.
SQL> select any_date_you_want('DE0E0100000000')
2 from dual
3 /
ANY_DATE_
---------
00-JAN-14
SQL>
SY.
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612217 is a reply to message #612216] |
Mon, 14 April 2014 08:48 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Ah! Behaviour has changed, it was the following one until at least 11.2.0.3 (for all 10.2.0.4+ and 11 versions I tested, this was again different in earlier 10g and 9i and below, I don't remember when in 10g it has changed):
SQL> create or replace
2 function any_date_you_want(
3 p_str varchar2
4 )
5 return date
6 is
7 v_date date;
8 begin
9 dbms_stats.convert_raw_value(p_str,v_date);
10 return v_date;
11 end;
12 /
Function created.
SQL> select any_date_you_want('DE0E0100000000') from dual;
ERROR:
ORA-01801: date format is too long for internal buffer
no rows selected
SQL> select to_char(any_date_you_want('DE0E0100000000'),'DD/MM/YYYY HH24:MI:SS') from dual;
TO_CHAR(ANY_DATE_YO
-------------------
00/00/0000 00:00:00
1 row selected.
[Updated on: Mon, 14 April 2014 08:50] Report message to a moderator
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612219 is a reply to message #612217] |
Mon, 14 April 2014 09:08 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Well, it is TO_CHAR that either throws an error or returns zero date when it gets confused with non-existing dates. We are talking about bad dates getting into a table. For example, I have no problem creating 99-jan-2014 99:99:99:
SQL> create table tbl
2 as
3 select any_date_you_want('DE0E0163636363') dt
4 from dual
5 /
Table created.
SQL>
And select using NLS_DATE_FORMAT default DD-MON-YY:
SQL> select *
2 from tbl
3 /
DT
---------
99-JAN-14
But with other format masks TO_CHAR returns zero date:
SQL> select to_char(dt,'dd-mon-yyyy hh24:mi:ss') from tbl;
TO_CHAR(DT,'DD-MON-Y
--------------------
00-000-0000 00:00:00
SQL> select to_char(dt,'dd-mm-yy hh24:mi:ss') from tbl;
TO_CHAR(DT,'DD-MM
-----------------
00-00-00 00:00:00
SQL> select to_char(dt,'hh24:mi:ss') from tbl;
TO_CHAR(
--------
00:00:00
But again, we are talking about bad dates in a table, so it is not TO_CHAR but rather DUMP we should use to get correct picture:
SQL> select dump(dt,16) d from tbl;
D
-----------------------------------
Typ=12 Len=7: dd,72,1,63,63,63,63
SQL>
SY.
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612222 is a reply to message #612219] |
Mon, 14 April 2014 10:18 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: I have no problem creating 99-jan-2014 99:99:99:
I have no doubt about this.
I just wanted to say if you use TO_CHAR on an invalid date it (on all version I worked) returns date 0 and if you don't use TO_CHAR you get a buffer error (still on the versions I worked on).
Here's your table with 10.2.0.4 and 11.2.0.1:
SQL> @v
Version Oracle : 10.2.0.4.0
SQL> create table tbl as select any_date_you_want('DE0E0163636363') dt from dual;
Table created.
SQL> select * from tbl;
ERROR:
ORA-01801: date format is too long for internal buffer
no rows selected
SQL> @v
Version Oracle : 11.2.0.1.0
SQL> create table tbl as select any_date_you_want('DE0E0163636363') dt from dual;
Table created.
SQL> select * from tbl;
ERROR:
ORA-01801: date format is too long for internal buffer
no rows selected
Edit: my default date format is "DD/MM/YYYY HH24:MI:SS".
[Updated on: Mon, 14 April 2014 10:20] Report message to a moderator
|
|
|
Re: how can i insert 00-jan-2014 into a date column [message #612223 is a reply to message #612222] |
Mon, 14 April 2014 10:28 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Ah! I see the misunderstanding, your data is wrong, it points to year 12214 not 2014, so format YY works when YYYY does not:
SQL> alter session set nls_date_format='DD-MON-YY';
Session altered.
SQL> select dt from tbl;
DT
-----------
99-JANV.-14
1 row selected.
SQL> select * from tbl where dt < to_date('9000','YYYY');
no rows selected
SQL> select * from tbl where dt > to_date('9999','YYYY');
DT
-----------
99-JANV.-14
1 row selected.
Edit:
Back to my nls default settings:
SQL> select any_date_you_want('78720163010101') from dual;
ANY_DATE_YOU_WANT('
-------------------
99/01/2014 00:00:00
SQL> select to_char(any_date_you_want('78720163010101')) from dual;
TO_CHAR(ANY_DATE_YO
-------------------
00/00/0000 00:00:00
They fix TO_CHAR to prevent it from displaying wrong dates but they do not fix date->string default conversion.
[Updated on: Mon, 14 April 2014 10:34] Report message to a moderator
|
|
|