CODEWITHSUNDEEP
sqloracle-databasedate
Oleksandr Stefanovskyi
Oleksandr Stefanovskyi

Reputation: 380

Convert Oracle VARCHAR2 to DATE and exclude invalid data

I have column column_name VARCHAR2 with various data. To convert it to DATE datatype I wrote following expression: 

SELECT TO_DATE(column_name, 'YYYY/MM/DD') FROM schema.table;

But it gives me error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered

*Action: Input year in the specified range

Column consist of NULL, space, 00000000 and string dates like "20161111".

To exclude invalid data I decided to use DECODE:

SELECT DECODE(column_name, 
              '', NULL, 
              '00000000', NULL, 
              TO_DATE(column_name, 'YYYY/MM/DD')) 
FROM schema.table;

But in this case I get a following error:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*Cause: Illegal year entered

*Action: Input year in the specified range

Data example:

| # | column_name |
|---|-------------|
| 1 | 00000000    |
| 2 |             |
| 3 | (null)      |
| 4 | 20161111    |

What am I doing wrong?

Is there any solution to exclude all invalid data without including it in cases of DECODE?

Upvotes: 0

Views: 736

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can just use CASE and REGEXP_LIKE():

SELECT (CASE WHEN REGEXP_LIKE(column_name, '^[12][0-9]{3}/[01][0-9]/[0123][0-9]$')
             THEN TO_DATE(column_name, 'YYYY/MM/DD')
        END)

Obviously, this is not a perfect solution, but it works under many circumstances.

Upvotes: 1

MT0
MT0

Reputation: 168291

Write a user-defined function that will handle the exceptions:

CREATE FUNCTION parse_Date(
  in_string     VARCHAR2,
  in_format     VARCHAR2 DEFAULT 'YYYY/MM/DD',
  in_nls_params VARCHAR2 DEFAULT NULL
) RETURN DATE DETERMINISTIC
AS
BEGIN
  RETURN TO_DATE( in_string, in_format, in_nls_params );
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

Then you can do:

SELECT parse_Date( column_name, 'YYYY/MM/DD' )
FROM   schema.table

Otherwise you could use regular expressions to match different valid date patterns.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

You could write a function like this:

CREATE OR REPLACE FUNCTION VARCHAR_TO_DATE(str IN VARCHAR2) RETURN DATE AS
BEGIN
   RETURN TO_DATE(str, 'YYYY/MM/DD');
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;
END;

Then use it as

SELECT varchar_to_date(column_name) FROM schema.table;

Any invalid strings will result in a NULL value.

Upvotes: 2

Related Questions