Birthday formula error
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22185
Printed Date: 02 May 2024 at 6:34am
Topic: Birthday formula error
Posted By: mosquito
Subject: Birthday formula error
Date Posted: 03 Jan 2017 at 9:08am
Hi all,
I have the following formula below. I am receiving the error " A day number must be between 1 and the number of days in the month" . The report is supposed to show birthdays in the week.
It had worked normally until yesterday, 1/2/17 but now it's failing. Ideas?
if month(CurrentDate) * 100 + day(CurrentDate) > Month({Command.birth_date}) * 100 + day ({command.birth_date})
then Date(Year (CurrentDate) , Month({Command.birth_date}), Day({Command.birth_date}))
else Date(Year (CurrentDate) , Month({Command.birth_date}), Day({Command.birth_date}))
|
Replies:
Posted By: kevlray
Date Posted: 04 Jan 2017 at 4:18am
Since it is not liking the day function. Then you have to look at your data and see if you have an incorrect date (Command.birth_date). It is unlikely that the CurrentDate function would return an incorrect date.
|
Posted By: mosquito
Date Posted: 04 Jan 2017 at 4:47am
|
command.birth_date is coming from the database, I did a Browse Data and the format looks correct on all entries. Is that what you mean by incorrect date?
|
Posted By: DBlank
Date Posted: 05 Jan 2017 at 5:43am
my guess is because 2016 was a leap year.
If your DB has any person with 2/29 as a DOB it is failing because it is trying to set this as 2/29/2017 which is not a valid day.
I am very confused by the formula you posted though as your THEN and ELSE are returning the same values so why bother with the IF?
You might consider using a datediff and dateadd function if you are trying to 'shift' the DOB to the current year with the DON month and day. This would account for leap years and use 2-28 as the DOB for those people on only current years that are not a leap year.
Example:
DATEADD('yyyy',datediff('yyyy',{command.birth_date},today),{command.birth_date})
|
|