Subject Re: [firebird-support] select birthdays from the next several days Arno Brinkman 2005-09-23T14:13:11Z
Hi,

>> Check on ((Month * 100) + Day)
>
> That's an interesting idea.
>
> I was skeptic, if it is working and I was true: this also has a problem,
> when the year is changing:
>
> currentday = 2005-12-22 = 1222
> currentday + 14 = 2006-01-06 = 106
>
> So it will list all the people, except the ones, which have birthday. ;-)

In fact it will list none, but you can make a working version if you include the year (for those
still trying ;-):

SELECT
PersonName,
Birthday
FROM
Persons
WHERE
EXTRACT(YEAR FROM CURRENT_DATE) * 10000 +
EXTRACT(MONTH FROM Birthday) * 100 +
EXTRACT(DAY FROM Birthday)
BETWEEN
EXTRACT(YEAR FROM CURRENT_DATE) * 10000 +
EXTRACT(MONTH FROM CURRENT_DATE) * 100 +
EXTRACT(DAY FROM CURRENT_DATE) and
EXTRACT(YEAR FROM CURRENT_DATE + 14) * 10000 +
EXTRACT(MONTH FROM CURRENT_DATE + 14) * 100 +
EXTRACT(DAY FROM CURRENT_DATE + 14)
or
(EXTRACT(YEAR FROM CURRENT_DATE) + 1) * 10000 +
EXTRACT(MONTH FROM Birthday) * 100 +
EXTRACT(DAY FROM Birthday)
BETWEEN
EXTRACT(YEAR FROM CURRENT_DATE) * 10000 +
EXTRACT(MONTH FROM CURRENT_DATE) * 100 +
EXTRACT(DAY FROM CURRENT_DATE) and
EXTRACT(YEAR FROM CURRENT_DATE + 14) * 10000 +
EXTRACT(MONTH FROM CURRENT_DATE + 14) * 100 +
EXTRACT(DAY FROM CURRENT_DATE + 14)

Not nice syntax to look at, but it should work :)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info