Oracle Sql To_date Error Handling
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the date exception in oracle workings and policies of this site About Us Learn more about Stack
Date Format Exception In Oracle
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs how to find invalid date in oracle Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join oracle check for valid date them; it only takes a minute: Sign up How to handle to_date exceptions in a SELECT statment to ignore those rows? up vote 6 down vote favorite 3 I have the following query that I am attempting to use as a COMMAND in a crystal report that I am working on. SELECT * FROM myTable WHERE to_date(myTable.sdate, 'MM/dd/yyyy') <=
Ora-01843 Not A Valid Month Oracle
{?EndDate} This works fine, however my only concern is that the date may not always be in the correct format (due to user error). I know that when the to_date function fails it throws an exception.. is it possible to handle this exception in such a way that it ignores the corresponding row in my SELECT statement? Because otherwise my report would break if only one date in the entire database is incorrectly formatted. I looked to see if Oracle offers an isDate function, but it seems like you are supposed to just handle the exception. Any help would be greatly appreciated. Thanks!! oracle crystal-reports share|improve this question asked May 11 '11 at 14:52 ntsue 67051845 2 Well you are supposed to store dates in DATE columns. Why don't you? –Tony Andrews May 11 '11 at 14:54 It's this legacy application that I work with at the job. They use strings because otherwise the dates default to the current date... I have no control over the DB –ntsue May 11 '11 at 15
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL isdate OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL pl sql to_date PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser
Oracle Exception
PrivilegeCatch exception from to_date function : TO_DATE«Date Timestamp Functions«Oracle PL/SQL TutorialOracle PL/SQL TutorialDate Timestamp http://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows FunctionsTO_DATESQL> SQL> SQL> DECLARE 2 bad_date EXCEPTION; 3 PRAGMA EXCEPTION_INIT (bad_date, -01843); 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE (TO_DATE ('13/13/99', 'MM/DD/YY')); 6 EXCEPTION 7 WHEN bad_date 8 THEN 9 DBMS_OUTPUT.PUT_LINE('Just twelve http://www.java2s.com/Tutorial/Oracle/0260__Date-Timestamp-Functions/Catchexceptionfromtodatefunction.htm months in a year...'); 10 END; 11 / Just twelve months in a year... PL/SQL procedure successfully completed. SQL> 13.21.TO_DATE13.21.1.Specifying a Datetime Format for TO_DATE() function13.21.2.Using the YY Format13.21.3.Using the RR Format13.21.4.TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY')13.21.5.TO_DATE function to convert from characters to dates explicitly13.21.6.TO_DATE(x[, format]) converts the x string to a datetime13.21.7.TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 200613.21.8.Specifying Times13.21.9.TO_DATE() with INSERT statement13.21.10.DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1'));13.21.11.DBMS_OUTPUT.PUT_LINE(TO_DATE ('6/1996'));13.21.12.DBMS_OUTPUT.PUT_LINE(TO_DATE ('12-APR-09'));13.21.13.DBMS_OUTPUT.PUT_LINE(TO_DATE ('19991205'));13.21.14.DBMS_OUTPUT.PUT_LINE(TO_DATE ('1/1/1'))13.21.15.Birthday before 194013.21.16.Catch exception from to_date functionjava2s.com |Email:info at java2s.com|© Demo Source and Support. All rights reserved.
9.4 Migrating to PostgreSQL PostgreSQL PostFAQ Schemas Deutsche FAQ Gotchas AboutContact Site-Info Links ibarwick's Github page Twitter (2ndQ) 2ndQuadrant Search Powered by Linux Apache http://sql-info.de/postgresql/notes/to_date-to_timestamp-gotchas.html mod_perl PostgreSQL9.5.4 RSS January 27, 2014 PostgreSQL | TO_DATE() / TO_TIMESTAMP() gotchas A recent thread on -general sparked my interest as I'm working with a large and heterogeneous http://www.orafaq.com/wiki/ORA-01858 code base recently converted from Oracle which makes heavy use of TO_DATE(), and it seems this function has a couple of gotchas worthy of consideration. 1. TO_DATE() does in oracle not really validate dates. Even if passed an invalid date, it will happily munge it into a nominally valid date - which is probably not what you want: # SELECT to_date('2014/02/31', 'YYYY/MM/DD'); to_date ------------ 2014-03-03 (1 row) A possible workaround is to explicitly cast the string literal to DATE, e.g.: # SELECT '2014/02/31'::DATE; ERROR: date/time field value exception in oracle out of range: "2014/02/31" LINE 1: SELECT '2014/02/31'::DATE; ^ # SELECT '2014/02/28'::DATE; date ------------ 2014-02-28 (1 row) but this won't work with as wide a range of date formats as TO_DATE(). If you really need to parse arbitrary date strings, a function like this might make things safer by converting the output date to the original format, comparing input and output strings and raising an error if the value fails a round-trip: CREATE OR REPLACE FUNCTION to_date_safe(p_date TEXT, p_format TEXT, OUT r_date DATE) LANGUAGE plpgsql AS $$ BEGIN r_date = TO_DATE(p_date, p_format); IF TO_CHAR(r_date, p_format) != p_date THEN RAISE EXCEPTION 'Input date % does not match output date %', p_date, r_date; END IF; END; $$; This will mean a slight performance hit, and there might be side-effects I haven't thought about yet, but it seems preferable to silently accepting an incorrect date. # SELECT to_date_safe('2014/02/31', 'YYYY/MM/DD'); ERROR: Input date 2014/02/31 does not match output date 2014-03-03 2. PostgreSQL's TO_DATE() is not Oracle's TO_DATE() In Oracle, the DATE da
have passed some wrong values, then Oracle responds back with this message. How to fix it[edit] If your session is set to default date format of DD-MON-YY, execute the following and you will receive the error message: SQL> select to_date('10-JAN-2001', 'DD-MM-YYYY') from dual; ERROR: ORA-01858: a non-numeric character was found where a numeric was expected no rows selected When you are converting a string to a date, you have specified that the date is being passed in DD-MM-YYYY format. But you have passed the date in DD-MON-YYYY format. As the month is expected as a number by Oracle, but you have passed a character, Oracle is unable to translate the string to a number. Do one of the following: SQL> select to_date('10-JAN-2001', 'DD-MON-YYYY') from dual 2 / TO_DATE('10 ----------- 10-JAN-2001 OR SQL> select to_date('10-01-2001', 'DD-MM-YYYY') from dual 2 / TO_DATE('10 ----------- 10-JAN-2001 When you are fetching strings from a table and converting them to dates, check the data in the table first before issuing the date format string. If the table contained strings, which are not date values and you tried to convert them to dates, then you would see this error being raised. There is no predefined exception to handle this error. So you would have to create a user-defined error to handle it. Retrieved from "http://orafaq.com/wiki/index.php?title=ORA-01858&oldid=16511" Category: Errors Navigation menu Views Page Discussion Edit History Personal tools Log in / create account Site Navigation Wiki Home Forum Home Blogger Home Site highlights Blog Aggregator FAQ's Mailing Lists Usenet News RSS Feeds Wiki Navigation Categories Recent changes Random page Help Search Tools What links here Related changes Special pages Printable version Permanent link Page information This page was last modified on 21 June 2016, at 16:21. Privacy policy About Oracle Wiki Disclaimers