Re: Date Format Problem -- illegal use of to_date

From: Arjan van Bentem <avbentem_at_DONT-YOU-DARE-cable.a2000.nl>
Date: Sat, 25 Jul 1998 15:36:42 +0200
Message-ID: <6pcn0t$35t$1_at_newton.a2000.nl>

J.,

I am afraid that you are (or have been) using to_date to convert a date to date. Really, Oracle can not convert a date to a date and might lose the century when you try to do so. Consider the following:

  select to_char( to_date( sysdate, 'DD-MON-YYYY')

                , 'DD-MON-YYYY')

  from dual;

If this code seems correct to you, then please run it in SQL*Plus. Most likely, you will get 25-JUL-0098. What happens is that Oracle, like I said, does not know how to convert a date to a date. So, it temporarily has to convert sysdate to a character string, using the default print format. You can see what this string look likes using

  select sysdate
  from dual;

As you don't supply a print format, the default print format is used. Most likely, this is DD-MON-YY, so you'll get 25-JUL-98. Next, Oracle converts this to a date, unsing the given DD-MON-YYYY format. No problem. Just like the 1 in 1-JUL-98 would be replaced by 01, Oracle will interpret 98 to be 0098. There you have it. This is not a bug, but just illegal use of the to_date function. It gets even more interesting when you try to print the year 2000, as the year 0000 never existed and causes Oracle to raise an exception.

You said it worked on your pc, but not on another. So, presumably, the default date format on your pc is DD-MON-RR or DD-MON-YYYY, while it is DD-MON-YY on the other. So, hopefully, you're just using code like this when printing the dates. If you used something simular when inserting or updating dates in tables, then the dates could be wrong in your tables! Below is some code to check all your tables for incorrect dates.

This whole problem could have been prevented when setting your default date format to DD-MON-RR or, even better, DD-MON-RRRR. The latter will interpreted both 98 and 1998 as 1998.

Arjan.

• File : sql/check_dates.sql
  --
  
• Application : General date check
  --
  
• Description : Check of centuries in all user date columns.
  --
  
• Creates and fills the table y2000_date_checks.
• All tables that belong to the current user
• will be checked for centuries other than 20
• and 21 in all date columns.
  --
  
• For some Oracle RDBMS versions, date
• 31-DEC-4712 is the maximum possible value and
• does not necessarily indicate a problem.
  --
  
• As this script might encounter invalid views
• or other errors, and as the checks may take
• a very long time to finish, results are stored
• in a table. This way, the script can be
• restarted whenever needed. This table can also
• be used to check the progress of the scrip, as
• all changes are committed directly.
  --
  
• Syntax : check_dates
  --
  
• History : Arjan van Bentem 01-Aug-1997 Initial Issue
  --
  set serveroutput on size 20000
  --
  
• The table might already exist due to restart of this script,
• so ignore any errors:
  --
  whenever sqlerror continue
  --
  create table y2000_date_checks ( check_date date not null , table_name varchar2(132) , column_name varchar2(132) , check_result varchar2(50) );
  --
  whenever sqlerror exit failure
  --
  
• Delete uncompleted checks in case of restart
  --
  delete from y2000_date_checks where check_result = 'Checking'; commit;
  --
  declare l_result integer; -- cursor c_DateCols is select usercol.table_name, usercol.column_name from user_tab_columns usercol where usercol.data_type = 'DATE' and usercol.table_name != 'Y2000_DATE_CHECKS' and not exists ( select null from y2000_date_checks checked where checked.table_name = usercol.table_name and checked.column_name = usercol.column_name and checked.check_result != 'Checking' );
  --
  
• Dynamic SQL function to execute any query which returns
• an integer value:
  --
  function ExecQuery( p_query in varchar2) return integer is l_cursorid integer; l_rowcount integer; l_result integer; begin l_result := null; -- l_cursorid := dbms_sql.open_cursor; dbms_sql.parse( l_cursorid, p_query, dbms_sql.native); dbms_sql.define_column( l_cursorid, 1, l_result); l_rowcount := dbms_sql.execute_and_fetch( l_cursorid, true); dbms_sql.column_value( l_cursorid, 1, l_result); dbms_sql.close_cursor( l_cursorid); -- if l_result is null then dbms_output.put_line( 'No result for ' || p_query); end if; -- return l_result; exception when others then if dbms_sql.is_open( l_cursorid) then dbms_sql.close_cursor( l_cursorid); end if; dbms_output.put_line( 'ExecQuery: error executing ' || p_query ); raise; end ExecQuery;
  --
  begin for r_DateCols in c_DateCols loop -- insert into y2000_date_checks ( check_date, table_name , column_name, check_result ) values ( sysdate, r_DateCols.table_name , r_DateCols.column_name, 'Checking' ); commit; -- l_result := ExecQuery ( 'select count(*)' || ' from ' || r_DateCols.table_name || ' where ' || r_DateCols.column_name || ' is not null' || ' and to_char( ' || r_DateCols.column_name || ', ''CC'')' || ' not in (''20'', ''21'')' ); -- if l_result != 0 then update y2000_date_checks set check_result = 'Errors: ' || to_char( l_result) where table_name = r_DateCols.table_name and column_name = r_DateCols.column_name; commit; else update y2000_date_checks set check_result = 'OK' where table_name = r_DateCols.table_name and column_name = r_DateCols.column_name; commit; end if; end loop; end; /
  --
  
• Show the results. Results will be kept in table
• y2000_date_checks for later reference:
  --
  set pagesize 60 column check_result format a10 heading 'Result' column table_name format a37 heading 'Table' column column_name format a29 heading 'Column'

select table_name, column_name, check_result
from   y2000_date_checks
where  check_result = 'OK';

select table_name, column_name, check_result

from y2000_date_checks
where check_result != 'OK';

select check_result, count(*)
from y2000_date_checks
group by check_result;

• End of script

Received on Sat Jul 25 1998 - 15:36:42 CEST