Stored procedure to check the time of particular column of date field.

need a store procedure to check the date field value.

Store procedure we need to check the time difference between sys date and Date column from a table.
Nandha Kumar BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
to find the difference for each row

select sysdate - your_date_column from your_table


once you have those results, what do you want to do with them?

Here's a basic framework, you'll need to fill in the "do something" part and add any filters that may apply to the query.

CREATE OR REPLACE PROCEDURE check_dates
IS
BEGIN
    FOR x IN (SELECT SYSDATE - your_date_column difference
                FROM your_table)
    LOOP
        -- do something with the difference
        DBMS_OUTPUT.put_line(x.difference);
    END LOOP;
END;
awking00Information Technology SpecialistCommented:
>>we need to check the time difference between sys date<<
When you say check the time difference, do you mean the differences of the times of sysdate and your date_column or the entire time difference between the two. sbstuber has shown you how to get the entire difference in days and it's a simple process to convert that to days, hours, minutes, etc. if that's what you need. If the desire is to get the difference in times of the two dates, that too can be done. Let us know.
Nandha Kumar BAuthor Commented:
HI,

Thank you for your suggestion!!

but what i want is difference in minute if SYSDATE - your_date_column difference FROM your_table is greater than 5 min i wanna BEGIN another loop.


Syntax
if (sysdate - my date column)>= 5 min
{

}
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
So, your if statement within the outer select loop would look like this:

if sysdate - my_date_colum >= (5/1440) then
begin
  --do other loop here
end
Nandha Kumar BAuthor Commented:
Yes
sdstuberCommented:
you can either put that IF  inside the loop above, or if you don't need to do anything with data that is less than 5 minutes then you could put your condition inside the driving query to only return data that satisfies your 5 minute window


CREATE OR REPLACE PROCEDURE check_dates
IS
BEGIN
    FOR x IN (SELECT SYSDATE - your_date_column difference
                FROM your_table
               WHERE SYSDATE - your_date_column >= 5 / 1440)
    LOOP
        -- do something with the difference
        DBMS_OUTPUT.put_line(x.difference);
    END LOOP;
END;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nandha Kumar BAuthor Commented:
Thank you works fine
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.