| Author |
Message |
mosquito
Newbie
Joined: 14 Dec 2016
Online Status: Offline
Posts: 2
|
  
Topic: Birthday formula error
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}))
|
 IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
| IP Logged |
|
mosquito
Newbie
Joined: 14 Dec 2016
Online Status: Offline
Posts: 2
|
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?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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})
|
| IP Logged |
|
