0

I am writing simple SELECT queries which involve parsing out date from a string.
The dates are typed in by users manually in a web application and are recorded as string in database.

I am having CASE statement to handle various date formats and use correct format specifier accordingly in TO_DATE function.

However, sometimes, users enter something that's not a valid date(e.g. 13-31-2013) by mistake and then the entire query fails. Is there any way to handle such rougue records and replace them with some default date in query so that the entire query does not fail due to single invalid date record?

I have already tried regular expressions but they are not quite reliable when it comes to handling leap years and 30/31 days in months AFAIK. I don't have privileges to store procedures or anything like that. Its just plain simple SELECT query executed from my application.

tumchaaditya
  • 1,267
  • 7
  • 19
  • 49
  • What version of Oracle? I don't suppose you're using 12.1? – Justin Cave Apr 07 '14 at 21:46
  • Pardon me for tag. Its 11 – tumchaaditya Apr 07 '14 at 21:47
  • @JustinCave - I didn't follow new features in 12.1 yet, do they have any new function that is more efficient than a home made one? – evenro Apr 07 '14 at 21:55
  • @evenro - No. But you can include a function definition in a query that only exists for the scope of that query in 12.1. So you can write a function to solve the problem even when you don't have `CREATE FUNCTION` privileges. – Justin Cave Apr 07 '14 at 22:03
  • @JustinCave neat! need to read the "new features".. I stopped being a DBA, so I'm not following it as tightly as I did... – evenro Apr 07 '14 at 22:06

2 Answers2

0

This is a client task.. The DB will give you an error for an invalid date (the DB does not have a "TO_DATE_AND_FIX_IF_NOT_CORRECT" function).

If you've got this error- it means you already tried to cast something to an invalid date.

I recommend doing the migration to date on your application server, and in the case of exception from your code - send a default date to the DB.
Also, that way you send to the DB an object of type DbDate and not a string.

That way you achieve two goals:
1. The dates will always be what you want them to be (from the client).
2. You close the door for SQL Injection attacks.

It sounds like in your case you should write the function I mentioned...

it should look something like that:

Create or replace function TO_DATE_SPECIAL(in_date in varchar2) return DATE is
  ret_val date;
begin
  ret_val := to_date(in_date,'MM-DD-YYYY');
  return ret_val;
exception
 when others then
  return to_date('01-01-2000','MM-DD-YYYY');
end;

within the query - instead of using "to_date" use the new function.
that way instead of failing - it will give you back a default date.

-> There is not IsDate function .. so you'll have to create an object for it...

I hope you've got the idea and how to use it, if not - let me know.

evenro
  • 2,626
  • 20
  • 35
  • Unfortunately, I have no say in how that web application works or how the dates are stored in DB(varchar instead of date). And I know there are invalid dates in there. I just want my query to circumvent them. – tumchaaditya Apr 07 '14 at 21:37
  • Are the wrong dates are in a string column within the DB? or are they part of the user input for the query? – evenro Apr 07 '14 at 21:38
  • I so wish that `TO_DATE` returned `NULL` in case of invalid date string instead of failing the query completely.. – tumchaaditya Apr 07 '14 at 21:38
  • They are in string column in DB. My query tries to parse them as date. – tumchaaditya Apr 07 '14 at 21:39
  • Do note that it will not be nearly as efficient as if you would have stored a DATE datatype, but I think this is the best I can offer (if you cannot change the database structure). If it has to be efficient, you can use function based index with it (after a very minor change of declaring it as a persistent function) and that would be the best you can do performance-wise – evenro Apr 07 '14 at 21:49
  • I don't think I have privileges to create function. I have mentioned it in last past of my question. – tumchaaditya Apr 07 '14 at 22:11
  • Then ask your DBA to create such a function for you - there's no way to not make such a casting fail with to_date (which you have to use for dates to work correctly) and you have to use a function you write for it.. no magical way to bypass it... – evenro Apr 07 '14 at 22:17
0

I ended up using crazy regex that checks leap years, 30/31 days as well. Here it is:

((^(0?[13578]|1[02])[\/.-]?(0?[1-9]|[12][0-9]|3[01])[\/.-]?(18|19|20){0,1}[0-9]{2}$)|(^(0?[469]|11)[\/.-]?(0?[1-9]|[12][0-9]|30)[\/.-]?(18|19|20){0,1}[0-9]{2}$)|(^([0]?2)[\/.-]?(0?[1-9]|1[0-9]|2[0-8])[\/.-]?(18|19|20){0,1}[0-9]{2}$)|(^([0]?2)[\/.-]?29[\/.-]?(((18|19|20){0,1}(04|08|[2468][048]|[13579][26]))|2000|00)$))

It is modified version of the answer by McKay here.
Not the most efficient but it works. I'll wait to see if I get a better alternative.

Community
  • 1
  • 1
tumchaaditya
  • 1,267
  • 7
  • 19
  • 49