Birthday Extraction

[ukwd]

Can anyone tell me how I can input a date range, then find anyone who has a birthday in that range?

My database has a field for date of Birth so I need to basically ignore the year part of the dates?

 

[IanWaterman]

If your date range is within same year you can use

date(year(minimum(?daterangeparam)), month(birthdate), day(Birthdate)) = {(?daterangeparam}

Gets more complicated if date range spans two or more years.

Ian

 

[solmskid]

ukwd,
you should be able to define a date parameter, be sure to check the "allow ranges" option. then in the report selection expert, the arguement would be something like this "{BirthDate} in ?DateRange. this would allow you to define any size range you need. {BirthDate} is a date field right?

 

[dgillz]

Ian,
If it spans more than one year just delete your record selection formula - everyone makes the list!

solmskid,
{DateofBirth} in {?DateRange} will not work. I was born in November of 1961. If you run the report for November of 2008 I would not show up on the list using {DateofBirth} in {?DateRange}, although I clearly had a birthday.

This is a great question that I do not have an answer to. I am still thinking. Maybe LB will weigh in.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dgillz]

Ian,

Sorry I just re-read your post and I am with you now. This is exactly my dilemma, if the range is 11/01/08 to 03/31/09 this is considerably tougher than if the range is all in the same year. In fact I do not know how to do this.

Leap year (Feb 29th) births pose another dilemma.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[CR85user]

Would the following be sufficient?:

if date(year(today),month({table.bdate}),day({table.bdate})) or
date(year(today)-1,month({table.bdate}),day({table.bdate}))
in {?Date range} then 'Birthday!' Else ''

Or would you need to consider next year also just in case?

if date(year(today),month({table.bdate}),day({table.bdate})) or
date(year(today)-1,month({table.bdate}),day({table.bdate})) or date(year(today)+1,month({table.bdate}),day({table.bdate}))
in {?Date range} then 'Birthday!' Else ''

There must be a cleaner way to acheive this.... I will have a ponder.

'J

CR8.5 / CRXI - Discovering the impossible

 

[solmskid]

ukwd,

you might try this in the selection arguement:

ToText{[birthdate}) startswith ?bdparm

where ?bdparm is a string parameter, enter the value as 3/12 for March 12.
you should check your default date format to validate the pattern.

 

[BryanLane]

I did one similar to this by creating a pair of parameters for {?StartDate} and {?EndDate} of the range I wanted and then a formula I called BDTY for Birthday This Year.

IF remainder(year({?StartDate}),4) > 0 and month({Table_.BirthDate}) = 2 and day({Table_.BirthDate}) = 29 // Tests for leap year babies in non-leap years

THEN date(year({?StartDate}),3,1) // Moves the birthday this year to March 1st (which is the date Washington State uses for liquor purchases in this case).

ELSE date(year({?StartDate}),month({Table_.BirthDate}),day({Table_.BirthDate})) // Creates birthday this year from month and day of BirthDate

My select statement only returns records where BDTY is between ({?StartDate} and ({?EndDate}.




"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein