Re: Birthday Query

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Wed, 24 Oct 2001 01:41:23 GMT
Message-ID: <3bd61a44.1072713750@news.alt.net>

On Tue, 23 Oct 2001 22:56:47 GMT, "Scott Mattes" <ScottMattes_at_yahoo.com> wrote:

>Brian,
> Yes, it does die on 1/1 (I sat down and did a test PL/SQL this time).
>
> Does it have to be a SQL solution? Could you use PL/SQL? At least that
>would be easier to read once you come up with a solution.
>

Oh, I do have a solution, and I did post it. Here it is the fragment.

AND(ADD_MONTHS(Birthday, MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'), TRUNC(Birthday, 'YYYY'))) BETWEEN (SYSDate - 15) AND (SYSDate + 15) OR ADD_MONTHS(Birthday, MONTHS_BETWEEN(TRUNC(SYSDATE,'YYYY'), TRUNC(Birthday, 'YYYY')) + 12) BETWEEN (SYSDate - 15) AND (SYSDate + 15))

Note the second check to capture when the year wraps. That is, when the birthday is 1/1 and we're running on 12/31.

I could use PL/SQL, but I don't see how it would be quicker. It's not like it could be DETERMINISTIC.

I did write a function, however, to use elsewhere. Here it is.

CREATE OR REPLACE Function Amount_Of_Days_This_Year (The_Date IN Date) RETURN NUMBER DETERMINISTIC IS
BEGIN

• This function will return the amount of days in the year passed in the date argument. RETURN TO_CHAR(TO_DATE('12/31/' || TO_CHAR(The_Date, 'YYYY'), 'MM/DD/YYYY'), 'DDD'); END Amount_Of_Days_This_Year; / CREATE OR REPLACE Function Days_Until (The_Date IN Date) RETURN NUMBER IS Today NUMBER; Target NUMBER; BEGIN
• This function returns the amount of days between today and the next occurence of a given date, disregarding the year.
• Change both dates into their day of year (similar to a Julian date, but only for this year) couterparts. Today := TO_CHAR(SYSDate, 'DDD'); Target := TO_CHAR(The_Date, 'DDD');
• Find out if the_date already passed this year, and treat it as if it were next year (or one big year). IF Target < Today THEN Target := Target + Amount_Of_Days_This_Year(SYSDate); END IF;

 RETURN Target - Today;

END Days_Until;
/

I wrote those a while back. I ought to change NUMBER to PLS_INTEGER.

Brian Received on Tue Oct 23 2001 - 20:41:23 CDT