Extract only day and month from a date field in crystal (8.5) query

[zenbino]

Hi All,

My first post and I promise I am not a student, merely a learner :7

I have run a search for 'birthday crystal' on this forum and come back with a lot of helpful stuff of which I am most appreciative of.

My situation is probably relatively simple but I am relatively new to crystal.

I'm using Crystal 8.5 and connecting to SQL Server.

A user will enter a date range in a reservation report which includes customers who have birthdays during the same period as their reservation. I.e. they have a reservation and a birthday during that entered range.

I want to filter only entries with a reservation and a birthday that falls within the user entered date range.
Below is our attempt where we make clauses for day's and months (not years)

day({custinfo.bday})>=day({user entered arrival}) and month({custinfo.bday})>=month( {user entered arrival}) and
day({custinfo.bday}) <= day( {user entered departure}) and month({custinfo.bday})<=month( {user entered departure})

RESULT:

If my birthday is 28.March

and a date range of 8.Feb to 19.April is entered by user:

my b'day only satisfies the month clauses but not the day (as 28 is higher than 19) and as such will not show up on the report, despite it clearly falling within the date range.

It is obvious my approach is too simple here. I feel that if there was a way we could ignore the year component it would be an easy fix.

Any sage advice would be greatly appreciated here.
Apologies if what I am asking is over simple or has already been covered.

zenb

 

[IanWaterman]

You nned to convert birth date into an aniversary. Problem arises if date range goes over a year end.

Try

@Anniversary

global numbervar year;
global numbervar month;
global numbervar day;

If month({custinfo.bday})>=month({user entered arrival}) then
year:= year({user entered arrival}) else year:= year( {user entered departure});

day:= day({custinfo.bday});
month:= month({custinfo.bday});

date(year, month, day);

You can now use this this formula in a filter in a date range.

It will be slow as you will need to bring back all data to Crystal and not be able to do any filtering on database.

You might also want to use another filter to bring back only clients travelling in data range and then filter that data set by the birthday formula

select will look some thing like

(Client travel date) >= startdate and
(Client travel date) <= enddate and
// this will parse to datebase
(@anniversary) >= startdate and
(@anniversary) <= enddate
// this will filter on report

Ian





Ian

 

[zenbino]

Wow.

That certainly looks exactly what I was looking for.
Thank you so much Ian for your generous explanation and your time.

i will pour over this and make sure i get it right

much obliged !

zenb