Home » SQL & PL/SQL » SQL & PL/SQL » how can i insert 00-jan-2014 into a date column
how can i insert 00-jan-2014 into a date column [message #612190] Mon, 14 April 2014 05:47 Go to next message
ramadurga
Messages: 5
Registered: June 2011
Location: chennai
Junior Member
using
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

how can i insert 00-jan-2014 into a date column?
Re: how can i insert 00-jan-2014 into a date column [message #612191 is a reply to message #612190] Mon, 14 April 2014 06:09 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
You need to have valid day of the month.
This will throw following error:

The Oracle docs note this on the ORA-01847 error:

       ORA-01847 day of month must be between 1 and last day of month
  
Cause: The day of the month listed in a date is invalid for the specified month. The day of the month (DD) must be between 1 and the number of days in that month. 
  
Action: Enter a valid day value for the specified month.
Re: how can i insert 00-jan-2014 into a date column [message #612192 is a reply to message #612191] Mon, 14 April 2014 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't. Why on earth would you want to?
Re: how can i insert 00-jan-2014 into a date column [message #612199 is a reply to message #612192] Mon, 14 April 2014 07:06 Go to previous messageGo to next message
ramadurga
Messages: 5
Registered: June 2011
Location: chennai
Junior Member
Actually...In PRODUCTION when we are taking backup to a backup table from a main table it is inserting some values like '00-xx-xxxx'.

Later we are taking all the rows from the backup table whose values are like above and updating the same in the backup table values taken from main table.

In developemnt to test that plsql script we need some rows with those type values.
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramadurga wrote on Mon, 14 April 2014 16:17
how 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 #612203 is a reply to message #612199] Mon, 14 April 2014 07:15 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then either

1) the data type of the column in production isn't date or
2) you mess up somehow (possible with some implicit conversion that might be going on) when you take the backup to the backup table.


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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramadurga wrote on Mon, 14 April 2014 17:36
Actually...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 #612205 is a reply to message #612199] Mon, 14 April 2014 07:16 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
ramadurga wrote on Mon, 14 April 2014 14:06
Actually...In PRODUCTION when we are taking backup to a backup table from a main table it is inserting some values like '00-xx-xxxx'
.

What does the format xx-xx-xxxx refer to exactly according to your application concept? A date ? If it is a date then where does 00 come from?
Re: how can i insert 00-jan-2014 into a date column [message #612206 is a reply to message #612204] Mon, 14 April 2014 07:22 Go to previous messageGo to next message
ramadurga
Messages: 5
Registered: June 2011
Location: chennai
Junior Member
no, it is a date data type bkg_d_o_b.
desc bkg_tb1;
Name Null? Type
----------------------------------------- -------- ----------------------------
BKG_PASS_NO VARCHAR2(16)
BKG_TAX_ID VARCHAR2(13)
BKG_CITIZEN_NO VARCHAR2(35)
BKG_CO_NAME VARCHAR2(40)
BKG_F_NAME VARCHAR2(20)
BKG_D_O_B DATE

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
ramadurga wrote on Mon, 14 April 2014 08:06
Actually...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 #612208 is a reply to message #612205] Mon, 14 April 2014 07:25 Go to previous messageGo to next message
Indu_Bandham
Messages: 1
Registered: April 2014
Junior Member
for a date type, its not at all possible to get this 00-jan-2014 value. impossible either to insert or update or to display.
Re: how can i insert 00-jan-2014 into a date column [message #612210 is a reply to message #612206] Mon, 14 April 2014 07:28 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
If it is date, yes it is normal that you get error, as 00 in not a valid day.
SQL> create table testtab(dateval date not null);

Table created.

SQL> insert into testtab(dateval) values(to_date('00-04-2014', 'dd-mm-yyyy'));
insert into testtab(dateval) values(to_date('00-04-2014', 'dd-mm-yyyy'))
                                            *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month


SQL> 

So start searching in the code that generates these values so see where does 00 comes from
Re: how can i insert 00-jan-2014 into a date column [message #612211 is a reply to message #612206] Mon, 14 April 2014 07:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramadurga wrote on Mon, 14 April 2014 17:52
no, it is a date data type bkg_d_o_b.


Can you post the result of
SELECT * FROM bkg_tb_1 WHERE to_char(bkg_d_o_b,'DD')=00;


I am just curious to see.
Re: how can i insert 00-jan-2014 into a date column [message #612213 is a reply to message #612211] Mon, 14 April 2014 07:45 Go to previous messageGo to next message
ramadurga
Messages: 5
Registered: June 2011
Location: chennai
Junior Member

SELECT BKG_D_O_B FROM BKG_tb1
WHERE BKG_D_O_B LIKE '00-%'
BKG_D_O_B
---------
00-00-0000

it is a production data thats why i can not attach a screen shot.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Indu_Bandham wrote on Mon, 14 April 2014 08:25
for 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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. Laughing

[Updated on: Mon, 14 April 2014 10:34]

Report message to a moderator

Previous Topic: Delete all levels of employees those who are under BLAKE?
Next Topic: (non)Matching nulls
Goto Forum:
  


Current Time: Fri Mar 29 03:57:50 CDT 2024