Oracle nasty dates finder for the .Net folks – Fix “Year, Month, and Day parameters describe an un-representable DateTime.”

If you are querying Oracle from .Net and you get this error:

Exception calling “Fill” with “1” argument(s): “Year, Month,
and Day parameters describe an un-representable DateTime.” —> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.

It is most likely because of DateTime column(s) having a value of 00000000 00:00:00 or because the year in the date is a negative year in BC (as opposed to AD).

This value is outside the MinValue and MaxValue range for a .Net DateTime data type. Read-on to see how you can find and fix/work around this issue. You will see a lot of posts on how to work around the issue but none that will help you locate the actual data with the issue (until now, that is šŸ˜‰ ).

Run the query below. This will return a SQL (please do substitute the table name with your table’s name). The SQL itself will not return the bad data but will return a SQL statement which in turn will.

SELECT 'SELECT ' || LISTAGG('TO_CHAR(' || column_name || ',''YYYYMMDD HH24:MI:SS'') AS ' || column_name
, ', ') WITHIN GROUP (ORDER BY rn)  ||
' FROM ' || table_name ||
' WHERE ' ||
  LISTAGG('TO_CHAR(' || column_name || ',''YYYYMMDD HH24:MI:SS'')  = ''00000000 00:00:00''
  OR TO_NUMBER(TO_CHAR (' || column_name ||', ''sYYYY'')) <= 0 '
, '  OR  ')
WITHIN GROUP (ORDER BY rn) AS NastyDatesFinderQuery
FROM
(
SELECT  table_name, column_name,
row_number() OVER ( PARTITION BY table_name  ORDER BY rownum) rn
FROM  dba_tab_columns where table_name='<YOUR_TABLE_NAME>' and data_type='DATE'
) a
GROUP BY table_name

The above SQL returns a SQL that in turn when run, will get the columns with bad DateTime data. The above query returned the below query for me

SELECT TO_CHAR (RECON_DATE, 'YYYYMMDD HH24:MI:SS') AS RECON_DATE,
   TO_CHAR (PRD_BEGIN_DATE, 'YYYYMMDD HH24:MI:SS') AS PRD_BEGIN_DATE,
   TO_CHAR (PRD_END_DATE, 'YYYYMMDD HH24:MI:SS') AS PRD_END_DATE,
   TO_CHAR (DATE_RECEIVED, 'YYYYMMDD HH24:MI:SS') AS DATE_RECEIVED
FROM MY_DUMMY_TABLE
WHEREĀ Ā Ā  TO_CHAR (RECON_DATE, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
   OR TO_NUMBER(TO_CHAR (RECON_DATE, 'sYYYY')) <= 0
   OR TO_CHAR (PRD_BEGIN_DATE, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
   OR TO_NUMBER(TO_CHAR (PRD_BEGIN_DATE, 'sYYYY')) <= 0
   OR TO_CHAR (PRD_END_DATE, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
   OR TO_NUMBER(TO_CHAR (PRD_END_DATE, 'sYYYY')) <= 0
   OR TO_CHAR (DATE_RECEIVED, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
   OR TO_NUMBER(TO_CHAR (DATE_RECEIVED, 'sYYYY')) <= 0

The area of interest is the WHERE clause that has the list of all DATE columns matching the invalid value.Running the query itself gave the the columns that had bad data that I was then able to fix.

Once you fix the data, your .Net code should not have issues. Alternatively, you could wrap the offending DATE column in a CASE statement like this in your SELECT to temporarily work-around the problem:

CASE
   WHEN TO_CHAR (RECON_DATE, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
        OR TO_NUMBER(TO_CHAR (RECON_DATE, 'sYYYY')) <= 0
THEN
   TO_DATE (NULL)
ELSE
   RECON_DATE
END

Oracle could store dates from BC in which case the Year will be negative. This is valid in Oracle but the standard .Net DateTime class again cannot represent that and will produce the same error at the top of this post. To find and fix, please do alter the query above (too lazy to update it now). The below workaround should (not tested) work for all cases. The “s” in “sYYYY” below is for the sign of the year.

CASE
   WHEN TO_CHAR (RECON_DATE, 'YYYYMMDD HH24:MI:SS') = '00000000 00:00:00'
       OR TO_NUMBER(TO_CHAR (RECON_DATE, 'sYYYY')) <= 0 THEN    TO_DATE (NULL) ELSE    RECON_DATE END 

Update: 02/29/2016 – Thanks to my co-DBA and good friend Chris Irving, as it turns out, Oracle has a package to identify bad dates.

 
REM  Purpose:   The purpose of this package is to identify invisibly 
REM             corrupted dates in a database of the form described in 
REM             the JDBC Alert Note:94185.1 and in Note:91207.1. 
REM             This form of corruption is subtle in that the displayed 
REM             form of the date looks correct, but comparisons and sorts 
REM             do not behave as expected. 
REM  https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=213979840487015&id=95402.1&_afrWindowMode=0&_adf.ctrl-state=1a1omtfkpv_4  

For example running the bad date checker after implemting the required package noted in the MOS article, would return an output like below in SQL*Plus. Note that it also tries to offer a solution to fix the issue.

 SQL> exec BadDate.CheckDB;

The output below shows SCHEMA,TABLE and COLUMN name for columns
which appear to contain corrupt dates of the form described in
the JDBC Alert Note:94185.1 and in Note:91207.1
Each bad column is followed by an UPDATE statement that
will repair the form of corruption described in these notes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

REM ATLAS.POOL_EFF_DATE_HIST has 2 suspect rows
rem Column POOL_EFF_DT_FROM has 2 Bad dates

UPDATE "ATLAS"."POOL_EFF_DATE_HIST"
SET "POOL_EFF_DT_FROM"="POOL_EFF_DT_FROM"+0
WHERE "POOL_EFF_DT_FROM"!="POOL_EFF_DT_FROM"+0
AND "POOL_EFF_DT_FROM">TO_DATE('19991231235959','YYYYMMDDHH24MISS')
;

If you found this useful to fix your issue or had to do additional workarounds, please post your comments.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s