CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Jay Khan
Jay Khan

Reputation: 98

Function that loops through a number of records and if a date format is not met store that row in a table

currently the function is working... tried some approaches

test case is that the column in the db = varchar , thus stores anything.. hence we need to come up witha way to validate that the time format is correct before loading occurs ... if something is inccorect it flags the row example: using a semi colon to separate time instead of a colon

begin 
    for i in (
        select id,time_in 
        from clock_load
        -- where NOT REGEXP_LIKE (replace(time_in,' ',''), '([01]?[0-9]|2[0-3]):[0-5][0-9]')
    )
    loop
        if to_date(i.time_in,'HH:MI:SS AM') <> TRUE
        then 
            dbms_output.put_line('the row is :' || i.id );
        end if;
                    
    end loop;
end;

Upvotes: 0

Views: 48

Answers (1)

Popeye
Popeye

Reputation: 35920

You can use to_date withon conversion error in IF condition as follows:

If to_date(i.time_in default null on conversion error,'HH:MI:SS AM') is null then

You can also check if time_in is not null as an extra condition if you dont want to consider null values.

Upvotes: 2

Related Questions