Using a case when to know if date format is right

Question

I want to migrate a table which contains some columns with dates. The issue is my dates are often in dd/mm/yyyyy HH24:MM:YYYY format. But sometimes it appears that the format is only dd/mm/yyyy, or blank.

I guess that's why I'm getting ORA-01830 when I'm trying to migrate the datas.

I tried

CASE WHEN TO_DATE(MYDATE,'DD/MM/YYYY')
then TO_DATE(MYDATE,'DD/MM/YYYY 00:00:00')
END AS MYDATE

But I'm not sure if it is possible to test the date format (and ofcourse it's not working).

Thank you

[Like this](http://stackoverflow.com/a/4078158/266304), but with your own expected formats in a sensible order? Presumably you're trying to undo a past bad decision to store dates as strings, which is good; but are you sure you don't have any stored as MM/DD/YYYY, say? You could catch those in the same way if they error but you can't always tell the difference. — Alex Poole, Dec 29 '14 at 10:39
I don't think OP's issue is with different date formats, the issue is the dates stored as string but with or without time portion. So, `to_date` should be able to convert the `string literal` into a date, for alue without time portion will turn out to be `00:00:00`. — Lalit Kumar B, Dec 29 '14 at 11:14
@LalitKumarB - true, as long as the strings are otherwise consistent, and the longest possible format model is used - the ORA-01830 would be thrown by a model without the time part. The original statement that actually throws that error would be useful. — Alex Poole, Dec 29 '14 at 11:18
If you really store dates as characters (which is a very bad idea), there's lots of functions you can use to check the validity. Maybe just use the function that checks the length of such a field (like LENGTH). In this case, it will differ between the two. But, that function on itself is no guarantee that the format is correct. — tvCa, Dec 29 '14 at 11:22

score 1 · Answer 1 · answered Dec 29 '14 at 13:23

TO_DATE cannot test date format, but you can do it. If Lalit's answer would not be enough, try something like

select
  case when my_date like '__/__/__' then to_date(my_date, 'dd/mm/yy')
       when my_date like '__-__-__' then to_date(my_date, 'dd-mm-yy')
       ...
  end

score 0 · Accepted Answer · answered Dec 29 '14 at 11:27

So you have the data type issue. DATE is stored as string literal. As you have mentioned that the date model has the DD/MM/YYYY part same, just that the time portion is either missing for some rows or the entire value is NULL.

For example, let's say your table have the values like -

SQL> WITH dates AS(
  2  SELECT 1 num, '29/12/2014 16:38:57' dt FROM dual UNION ALL
  3  SELECT 2, '29/12/2014' FROM dual UNION ALL
  4  SELECT 3, NULL FROM dual
  5  )
  6  SELECT num, dt
  7  FROM dates
  8  /

       NUM DT
---------- -------------------
         1 29/12/2014 16:38:57
         2 29/12/2014
         3

SQL>

TO_DATE with proper format model should do the trick.

Let's stick to a format model first.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

Now, let's use TO_DATE to explicitly convert the string literal to date.

SQL> WITH dates AS(
  2  SELECT 1 num, '29/12/2014 16:38:57' dt FROM dual UNION ALL
  3  SELECT 2, '29/12/2014' FROM dual UNION ALL
  4  SELECT 3, NULL FROM dual
  5  )
  6  SELECT num, to_date(dt, 'dd/mm/yyyy hh24:mi:ss') dt
  7  FROM dates
  8  /

       NUM DT
---------- -------------------
         1 29/12/2014 16:38:57
         2 29/12/2014 00:00:00
         3

SQL>

Using a case when to know if date format is right

2 Answers2