Search for birthdays

[jcs02]

Hi,
i have created a simple adress database
now im looking for a function / macro that looks for the next birthdays in my database

-> search for all entries with (persons) birthday within the next 3 days


fieldname fieldtype
birthday date[date]

Regards

Juergen

[Villeroy]

Hi Jürgen,
Are we talking about an embedded database of type hsqldb? See status bar of the datebase view.

[jcs02]

Hello Villeroy,
yes its an internal database, HSQL database engine.

greets

Juergen

[Villeroy]

Fine. Let's go. It is near impossible because the current version of hsqldb (used in OOo2.3.1) lacks some important functions for date calculation. hsqldb as a stand-alone database engine is documented here: http://hsqldb.org/web/hsqlDocsFrame.html
Hsqldb provides a function DATEDIFF returning the count of days/months/years between two dates and functions DAY, MONTH, YEAR returning the day/month/year number, and then there is a function DAYOFYEAR. But there is no function like DATEADD for adding some amount of time units to a given date.
A string value which represents some date in ISO format ('1999-12-31') can be converted to a date value. We get this year's anniversary from a given date 1999-12-31 by concatenation of 3 numbers with two dashes, utilizing functions YEAR(CURDATE()) and the concatenation operator ||:
Let "Date" be 1999-12-31 and CURDATE() = 2008-01-06
YEAR(CURDATE())||'-'||MONTH("Date")||'-'||DAY("Date") ==> string '2008-12-31'
CONVERT('2008-12-31', DATE) ==> date value 2008-12-31
DATEDIFF('dd', 2008-12-31, CURDATE()) ==> 360 days ('dd' specifies that we want the number of days)
If this year's anniversary has passed, the next anniversary will happen next year, so we have to add one year to the current year:
YEAR(CURDATE()) + CASEWHEN( DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()), 1, 0 )

Having a test table "Dates" with a field "Date" I can get the next anniversary of "Date" and filter by some amount of days. Replace "Date" and "Dates" with your names of field and table, create a query in SQL view and check the "SQL" button for "direct SQL" mode (the query will be interpreted by the hsqldb engine rather than the office):

Code: Select all

SELECT
	"Date" AS "Day Of Birth",
	CONVERT(
		YEAR(CURDATE())
		+CASEWHEN(
			DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()),
			1,
			0
		)
		||'-'||MONTH("Date")||'-'||DAY("Date"),
		DATE
	) AS "Next Birthday",
	YEAR(CURDATE())
	+CASEWHEN(
		DAYOFYEAR("Date")<DAYOFYEAR(CURDATE()),
		1,
		0
		)
	-YEAR("Date") AS "Age"
FROM "Dates"
WHERE DATEDIFF('dd', CURDATE(), "Next Birthday")<60

Important Note: This method of adding years, concatenation to string and conversion back to date value works well with years and months, even with negative values and months >12, but unfortunately it does not work reliably with days. So do not use this method for calculation of day-offsets. Appropriate date/time functions are announced for future versions of hsqldb.

[DrewJensen]

You can create a query as:

Code: Select all

SELECT "datetable".* FROM "datetable"
WHERE  DAYOFYEAR( "birthday " ) >= DAYOFYEAR( CURRENT_DATE )
AND  DAYOFYEAR( "birthday " ) - DAYOFYEAR( CURRENT_DATE )  <= 3

To see how that would look in the GUI designer:

Query_designer_GUI

birthday_1.png (9.27 KiB) Viewed 5081 times

You could replace the number of days with a parameter so the query is

Code: Select all

SELECT "datetable".* FROM "datetable"
WHERE  DAYOFYEAR( "birthday " ) >= DAYOFYEAR( CURRENT_DATE )
AND  DAYOFYEAR( "birthday " ) - DAYOFYEAR( CURRENT_DATE )  <= :NumberOfDays

Now whenever the query is run a dialog box will open and allow you to enter the number of days you want to use.

HTH

Drew

Edit: NOTE - there is a bug in OO.o 2.3.1 ( maybe older ) such that you can not turn the GUI design view off with this type of query. Not the worst bug but it is there none the less.

[Villeroy]

You can create a query as:

Code: Select all

SELECT "datetable".* FROM "datetable"
WHERE  DAYOFYEAR( "birthday " ) >= DAYOFYEAR( CURRENT_DATE )
AND  DAYOFYEAR( "birthday " ) - DAYOFYEAR( CURRENT_DATE )  <= 3

Looks promising but fails if CURRENT_DATE is near the end of year.

Code: Select all

SELECT "datetable".* FROM "datetable"
WHERE  DAYOFYEAR( "birthday " ) >= DAYOFYEAR( {d '2007-12-31'} )
AND  DAYOFYEAR( "birthday " ) - DAYOFYEAR( {d '2007-12-31'} )  <= 3

[DrewJensen]

hmmm- well yes and I realized it would also fail for Leap Year babies...

[jcs02]

Hi all,
thanks for your answers.
I tried a lot ... but I think the best method is to change completely to a mysql database

It should work with windows too, i test it only on some linix boxes

So, thanks again for your great help !!

Regards

Juergen