Thursday, June 21, 2012

Oracle DATE Joins Fail for no Obvious Reason


Brief Description:  Attempting to join columns of the same DataType results in errors like the below, or produce no results when there are valid joins in the tables
ORA-01858:  a non-numeric character was found where a numeric was expected

image
Problem:
I had a mildly (okay, deeply) frustrating couple of hours being beaten up by this problem.   I had two tables, one a dimension with general date info and another a fact table with a couple of date columns.  All three columns showed as DATE datatype, but while column1 joined to the date dimension without issue, column2 failed explosively or returned no results and with a number of different errors depending on what variety of date conversion, no date conversion, convert to string, then to date, convert to date with the month spelled out...etc.  All dates looked the same format in query results (in my case using Toad).  My management studio was displaying in the painfully old 'DD-MON-YY' format (which may be an artifact of this particular database having been continuously upgraded for years).  Although I can't swear to what is actually happening under the covers, forcing the NLS_DATE_FORMAT for the session (or in the case of Toad, the startup script) fixed the problem for me.  This may not be a problem in SQL*Plus, but I've had my share of frustration with NLS_DATE in SQL*Plus, so it's possible it's also applicable there.
In Toad:
To fix for this session only:
ALTER SESSION SET NLS_DATE_FORMAT='MM-DD-YYYY';
To fix in Toad startup script:
  • On the ribbon menu go to View/Toad Options
  • Select Scripts
  • Under Login scripts edit glogin.sql
  • If this is the first time you've edited the script, add the text below under the comment block, otherwise, enter it below the last operation
  • BEGIN
       EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''MM-DD-YYYY''';
    END;
image

No comments:

Post a Comment