Equvivalent function for ISDATE()
Srinivasan Subramanian, July 02, 2001 - 2:51 pm UTC
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?
Mark, October 20, 2005 - 5:03 pm UTC
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
Monika, January 24, 2008 - 1:08 pm UTC
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
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
A reader, January 25, 2008 - 6:40 am UTC
Good Function for ISDATE check
Ganesh, May 17, 2010 - 7:17 am UTC
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
Aji, November 18, 2010 - 5:52 am UTC
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
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
Steven Meyer, November 07, 2011 - 1:01 pm UTC
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.
Steven Meyer, November 07, 2011 - 2:20 pm UTC
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
Pepe Llavori, February 14, 2012 - 6:23 am UTC
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
Rajeshwaran, Jeyabal, October 27, 2020 - 5:22 am UTC
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
Rajeshwaran, Jeyabal, October 27, 2020 - 6:39 am UTC
October 28, 2020 - 2:51 am UTC
muito util , very usefull
Daniel Daniele, June 23, 2021 - 12:42 pm UTC
muito util , very usefull
muito obrigado
thanks