This content has been marked as final. Show 11 replies
Below is the example code which you can do for Date Check. Create a function for this, hope it will help you
please mark if it help you or correct
CREATE OR REPLACE FUNCTION F_DATE(v_date IN VARCHAR2) RETURN NUMBER IS v_date1 DATE; BEGIN select to_date(v_date) into v_date1 from dual; RETURN 1; Exception WHEN Others THEN RETURN 0; END; ---------------------------------------------- SELECT F_DATE('01-JAN-09') FROM DUAL; -- Returns 1 SELECT F_DATE('111111') FROM DUAL; -- Returns 0
What is the purpose of this varchar data type? If it is a date, why not create it to be date datatype.
Alternatively, you can always use your varchar as date data type.
What is the format of date you need?
You can use to_char and to_Date functions to manipulate your varchar data type and treat it like date.
you can try:
create or replace your_prodecure_name ( p_date in varchar2 ) is
begin --> begin own exception handler
l_date := to_date ( p_date, 'dd.mm.yyyy' ); --> use your date-format
then raise_application_error ( -20000, 'Not a valid date in your_prodecure_name.' );
end; --> end own exception handler -- you have then to catch this exception in your forms-code
Try something like this:
create function isdate(InTxt in varchar2) return boolean is
dummy := to_date(InTxt, 'dd-mm-yyyy');
when value_error then
I tried the exact same function....but when i am using the function and the date is not in a correct format then it is giving me an error saying "not a valid month"
hi navnit punj,
I am making an application where the user is bulk uploading excel files. Now in one of the columns of the excel file suppose to be a date . But if the user makes a mistake of typing some wrong value then it will go to an error record table prompting him to reupload after rectifying the errors.
Did you understand now y i need it ?
I used this function but still i am getting the same error. When the date is a valid date then the function returns 1 buttt when the date is not valid then it gives me an error saying not a valid month. the exception is not getting handled .
I am using the function like this :
select isdate1('08-13-2009') from dual; (this statement is giving me an error not returning 0).
Tell me if i am doing this correct
You have to pass a VARCHAR value in F_DATE funtion as you can see above this as
SELECT F_DATE('08-13-2009') FROM DUAL; ------Return 0 in my solution
F_DATE(v_date IN VARCHAR2)
here v_date is an input parameter with datatype VARCHAR.
IF you write DATE data type here then you will get error.
yeah danish....now its working for me too once i replaced the F_DATE function exactly the same way told here.....
let me just check it using it in my stored procs as well..
If it is working then please mark the answer as CORRECT. ;)
yeah sure...danish....here u go...!!!