Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fatima.

Asked: March 18, 2018 - 6:34 am UTC

Last updated: June 13, 2020 - 9:45 am UTC

Version: APEX

Viewed 10K+ times! This question is

You Asked

Hi Tom,

i have a question regarding some expiry dates that I have in a table and need to create a function that will check for those dates and will notify before they expired in two weeks.

i am very new to this and hoping that you might help me.

thank you.

and Connor said...

Not really sure what you're after, but here's a simple demo

SQL> create table t (
  2    id int,
  3    expiry date);

Table created.

SQL>
SQL> insert into t values (1,sysdate-10);

1 row created.

SQL> insert into t values (2,sysdate+1/2);

1 row created.

SQL> insert into t values (3,sysdate+9);

1 row created.

SQL> insert into t values (4,sysdate+16);

1 row created.

SQL> insert into t values (5,sysdate+17);

1 row created.

SQL> insert into t values (6,sysdate+25);

1 row created.

SQL>
SQL> select * from t;

        ID EXPIRY
---------- ---------
         1 10-MAR-18
         2 21-MAR-18
         3 29-MAR-18
         4 05-APR-18
         5 06-APR-18
         6 14-APR-18

6 rows selected.

SQL>
SQL> select id,
  2         'Expires in '||(expiry-sysdate)||' days'
  3  from   t
  4  where  expiry between sysdate and sysdate + 14;

        ID 'EXPIRESIN'||(EXPIRY-SYSDATE)||'DAYS'
---------- ----------------------------------------------
         2 Expires in .5 days
         3 Expires in 9 days


So you can see how we can pick up those rows that will 'expire' in the next 14 days (2 weeks). In terms of notifying someone, search this site for UTL_MAIL or UTL_SMTP for examples of how to send someone an email (if that is what you had in mind).

Rating

  (4 ratings)

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

Comments

Further explanation

Fatima 512, March 25, 2018 - 6:38 am UTC

Thank you so much for your time and effort.

My current goal is to create a logic that will check for me the expiration of vehicle registration date, so for example
i have a vehicle with the number plate 4774 and assuming that it will expire on the 8th of April (two weeks prior from now), I am facing a problem related to creating such a logic .

i tried this :

SELECT DISTINCT
VEHICLE_NUMBER ,
VEHICLE_TYPE_PARENT,
EXPIRY_DATE
FROM
FM_VEHICLES_REGISTRATION
WHERE
TO_DATE(EXPIRY_DATE,'DD-MM-YYYY') < TO_DATE (SYSDATE,'DD-MM-YYYY') + 14;
Connor McDonald
March 25, 2018 - 11:28 am UTC

and ?

Divya, June 08, 2020 - 1:33 pm UTC

Thank you so much!
It was really help full.
Connor McDonald
June 10, 2020 - 1:15 am UTC

glad we could help

A reader, June 10, 2020 - 7:05 am UTC

select TO_DATE (SYSDATE,'DD-MM-YYYY') + 14 from dual;

For me it returns 24-06-0020 :)
Chris Saxon
June 11, 2020 - 3:23 pm UTC

TO_DATEing a DATE => implicit conversion :(

The database first converts this to a string using your session's NLS settings.

A reader, June 12, 2020 - 7:51 am UTC

Yeah, and this is where I'd prefer exception to be thrown instead of doing to_char/to_date behind the scenes.
Connor McDonald
June 13, 2020 - 9:45 am UTC

I agree - I would love an init.ora parameter for implicit conversions, eg

implicit_conversion = DEFAULT or ERROR

but can you *imagine* how many apps would immediately break :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.