Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Srinivasan.

Asked: July 01, 2001 - 11:27 pm UTC

Answered by: Tom Kyte - Last updated: October 28, 2020 - 2:51 am UTC

Category: SQL*Plus - Version: 8 and above

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase.

If my Ascii file contains data which is not of date datatype, I want to insert a NULL instead of loading some wrong data or getting an error in sqlload.



Thanks for your Help
Srini

and we said...

You can simply:

ops$tkyte@ORA817.US.ORACLE.COM> create or replace
function my2date( p_str in varchar2 ) return date
2 is
3 begin
4 return to_date( p_str );
5 exception
6 when others then
7 return null;
8 end;
9 /

Function created.


And then in sqlldr, use:


....
date_field "my2date(:date_field)",
....



and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Equvivalent function for ISDATE()

July 02, 2001 - 2:51 pm UTC

Reviewer: Srinivasan Subramanian from Burlingame, CA, USA

Tom, Thanks a ton,
I'm in the Learning process, It didn't strike me to write a function on my own. It has solved many problems of mine.

Possible improvement?

October 20, 2005 - 5:03 pm UTC

Reviewer: Mark from Boston, MA

This was great, but I think I made this more useful with a format picture variable:

Assuming the nls_date_format is 'DD-MON-YY', then:

16:51:41 HT4@PHYDB001-LIVE> select to_date('01/01/05') from dual;
select to_date('01/01/05') from dual
*
ERROR at line 1:
ORA-01843: not a valid month


Elapsed: 00:00:00.79
16:55:43 HT4@PHYDB001-LIVE> select to_date('01-JAN-05') from dual;

TO_DATE('
---------
01-JAN-05

1 row selected.


CREATE OR REPLACE FUNCTION MY2DATE (p_str IN VARCHAR2
,format_picture IN VARCHAR2
)
RETURN DATE
IS
BEGIN
RETURN TO_DATE(p_str, format_picture);
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
/
Function Created.

Elapsed: 00:00:00.78
16:56:02 HT4@PHYDB001-LIVE> select to_date('01/01/05', 'mm/dd/yy') from dual;

TO_DATE('
---------
01-JAN-05

1 row selected.

Allows you to supply the date format picture.

:)


Is date giving unexpected result

January 24, 2008 - 1:08 pm UTC

Reviewer: Monika from UK

Hi tom,

I'm using isdate function provided by you (pasted below)
**********
create or replace function isdate
 ( p_string in varchar2,
     p_fmt in varchar2 := null)
      return varchar2
    as
        l_date date;
    begin
        l_date :=
     to_date(p_string,p_fmt);
     return 'TRUE';
   exception
       when others then
           return 'FALSE';
   end;
**********


But to my surpise it is taking 
'20092007' and '2007/2007' to be a valid date and converting it to a date but I want them to be reported as error pl refer below


SQL> SELECT TO_DATE('2009/2007','DD/MM/YYYY')  FROM DUAL;

TO_DATE('
---------
20-SEP-07

SQL> SELECT TO_DATE('20092007','DD/MM/YYYY')  FROM DUAL;

TO_DATE('
---------
20-SEP-07

SQL> SELECT TO_DATE('2010/2007','DD/MM/YYYY')  FROM DUAL;

TO_DATE('
---------
20-OCT-07

Can you please explain this behaviour

Thanks & Reagrds
Monika

Tom Kyte

Followup  

January 24, 2008 - 1:45 pm UTC

use FX for Format eXact

else, it is very "forgiving" as you have seen.

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> select to_date( '2009/2007', 'dd/mm/yyyy' ) from dual;

TO_DATE('2009/2007',
--------------------
20-sep-2007 00:00:00

ops$tkyte%ORA10GR2> select to_date( '2009/2007', 'fxdd/mm/yyyy' ) from dual;
select to_date( '2009/2007', 'fxdd/mm/yyyy' ) from dual
                *
ERROR at line 1:
ORA-01861: literal does not match format string

***** as always

January 25, 2008 - 6:40 am UTC

Reviewer: A reader from UK


Good Function for ISDATE check

May 17, 2010 - 7:17 am UTC

Reviewer: Ganesh

My entry is a late response to the post, but I was able to use the function provided to validate my data. Just wanted to say thank you for your aid.

Thanks a ton! :)

Need an SQL solution

November 18, 2010 - 5:52 am UTC

Reviewer: Aji from India

Hi Tom,

I was just wondering if there is an SQL only solution to validate date values? i.e validating without using PLSQL functions.

We have a scenario where 1.5 million records in a staging table have to be validated and date value is stored as a VARCHAR2 column in 'YYYYMMDD' format. The validation error need to be written into another column of the same staging table.

We could use this isdate function, but was just checking if there is any other way.

Thanks,
Aji
Tom Kyte

Followup  

November 19, 2010 - 7:11 am UTC

since you have just yyyymmdd - you could easily optimize this. Just substr and validate.

what is the valid range of years you have? say it is 1990 through 2010.

then

case when substr(dt,1,4) not between '1990' and '2010' then 0 else 1 end

mm must be between '01' and '12' right - that is easy to check. case it.

then dd must be between '01' and '31' (in general). case it.

then, all we need to do is make sure it is between '01' and '30' for a certain set of months (case it)

or 28 for feb - unless leap year (case it)

use the fact you have a fixed format

Another handy date function

November 07, 2011 - 1:01 pm UTC

Reviewer: Steven Meyer from San Jose, CA USA

I was writing an application that required that some records be sorted by a "date" field.

I put date in quotes because the field is a string of characters that are sometimes "999999" or "000000".

To make matters worse, when the data actually is a date, it is not in format that can be sorted as is (ie "rrmmdd") but instead is "mmddrr", and since it has 2 digit years, sorting on sub-strings will put 1999 (99) after 2001 (01).

Also, there were some other "dates" with different formatting.

Finally, there were times that the output should be formatted differently.

So I created a SQL callable function, taking parameters for the "date" data, an input format, and an output format, all as VARCHAR2.

The function will try to convert the input "date" to DATE using the provided input format, and then back to VARCHAR2 using the provided output format.

If successful, the function returns the final output from TO_CHAR().

If it fails, the input VARCHAR2 was returned as is.

This allowed my code to do . . .

SELECT ...
my_to_date(the_input_string, 'mmddrr', 'dd-Mon-yyyy')
...
ORDER BY my_to_date(the_input_string, 'mmddrr', 'yyyymmdd')

. . . with the caveat that when they provide invalid data such as "999999" or "000000", then the field will be sorted as it is, without conversion to date.

Business was happy with that.

Watch out for NULL values and their often unexpected effect on TRUE/FALSE tests.

November 07, 2011 - 2:20 pm UTC

Reviewer: Steven Meyer from San Jose, CA USA

When the input data can include NULL values, the code below will result in incorrect results.

case when substr(dt,1,4) not between '1990' and '2010' then 0 else 1 end 


When DT is NULL, the WHEN test is FALSE, so the ELSE value is returned.

Better:

case when substr(dt,1,4) between '1990' and '2010' then 1 else 0 end 


With this test, a NULL will return 0.

Easyer

February 14, 2012 - 6:23 am UTC

Reviewer: Pepe Llavori from Spain, Valencia

You can write in the where something like:

WHERE TO_DATE(NUMBER_TO_EVALUATE,'YYYMMDD') =
TO_DATE(NUMBER_TO_EVALUATE,'YYYMMDD')

If 'NUMBER_TO_EVALUATE' is in the right format your query will return the values, otherwise, it will fail.

doubt on validate_conversion function

October 27, 2020 - 5:22 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

could you please help us to understand why validate_conversion returns '1' is both cases?

shouldn't be returning '0' for first row, since the format doesn't matches with the value provided for it. kinldy clarify.

this was on 18c XE instance.

demo@XEPDB1> select x,
  2     validate_conversion(x as timestamp, 'yyyy-mm-dd hh24:mi:ssxff') c1
  3  from (
  4  select '16-sep-20' x from dual
  5  union all
  6  select '2019-10-21 00:00:00.000000' x from dual
  7      )
  8  /

X                                  C1
-------------------------- ----------
16-sep-20                           1
2019-10-21 00:00:00.000000          1

doubt on validate_conversion function

October 27, 2020 - 6:39 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team:

please ignore the above request, after reading about "Format Extract" from documentation, was able to solve it by myself. thanks.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A

demo@XEPDB1> select x,
  2    validate_conversion(x as timestamp, 'FXyyyy-mm-dd hh24:mi:ssxff6') c1
  3  from (
  4  select '16-sep-20' x from dual
  5  union all
  6  select '2019-10-21 00:00:00.000000' x from dual
  7     )
  8  /

X                                  C1
-------------------------- ----------
16-sep-20                           0
2019-10-21 00:00:00.000000          1

Connor McDonald

Followup  

October 28, 2020 - 2:51 am UTC

See here

https://connor-mcdonald.com/2016/01/27/automatic-date-formats/