0

I have made a function in SQL to calculate the Age from the Birthday and it is like this: 

FUNCTION [dbo].[GetAge] (@birthday datetime, @date datetime)
RETURNS int
AS
BEGIN
return datediff(SECOND, @birthday, @date) / (365.23076923074 * 24 * 60 * 60)
END

The birthday is of format : 1963-01-01 00:00:00.000

My problem is that when I call the function like this :

SELECT dbo.GetAge(birthday, '2014-12-17 00:00:00')
  FROM [dbo].[Users]
GO

it says:

 Msg 535, Level 16, State 0, Line 3
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

BUT I call the same function with a date like :

SELECT dbo.GetAge(birthday, '1963-01-01 00:00:00')
      FROM [dbo].[Users]
    GO

I get the results.. So I don't understand what is the problem.

Pls help me and thank you in advance

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Amehiny
  • 125
  • 4
  • 16
  • 1
    Perhaps this will help you: http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate – CodeLikeBeaker Dec 17 '14 at 20:33
  • 3
    This is a very odd way to calculate the age. – Gordon Linoff Dec 17 '14 at 20:34
  • I assume this must be sql server? You should only tag the relevant DBMS that you are working with. The reason for the error is because DATEDIFF returns an int and the amount of seconds between whatever value is in birthday and 2014-12-17 is greater than 2,147,483,647 which is the max value for an int. – Sean Lange Dec 17 '14 at 20:37
  • One more thing the function works when the date is up to 2008 after that it says overflow. I think there is range in datediff by default. Do you have any idea ??? – Amehiny Dec 17 '14 at 20:37
  • Yes I have an idea...read my comment above. – Sean Lange Dec 17 '14 at 20:51
  • possible duplicate of [How to calculate age in T-SQL with years, months, and days](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) – pseudocoder Dec 17 '14 at 20:52

5 Answers5

1

The error says it all. "Try to use datediff with a less precise datepart"

return DATEDIFF(DAY, '1963-01-01 00:00:00', '2014-12-17 00:00:00') / (365.23076923074)
Spock
  • 4,700
  • 2
  • 16
  • 21
  • 1
    Dividing by the average number of days in all years is not going to produce accurate results... – pseudocoder Dec 17 '14 at 20:51
  • Totally agree, but the question was how to fix the error, not calculate an DOB accurately to the last second – Spock Dec 17 '14 at 20:54
0

Seems obvious..the number of seconds from a user's birthday to today is too many for whatever datatype MySQL uses for DATEDIFF. But it's not too many from 1/1/1963.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • You could use DATEDIFF(YEAR...) instead of DATEDIFF(SECOND...), and then you wouldn't have to do the extra math "/ (365.23076923074 * 24 * 60 * 60)" – Tab Alleman Dec 17 '14 at 20:44
0

Change your function to use a less precise datepart, i.e. minute instead of second.

FUNCTION [dbo].[GetAge] (@birthday datetime, @date datetime)
RETURNS int
AS
BEGIN
return datediff(MINUTE, @birthday, @date) / (365.23076923074 * 24 * 60)
END

OR hour

FUNCTION [dbo].[GetAge] (@birthday datetime, @date datetime)
RETURNS int
AS
BEGIN
return datediff(HOUR, @birthday, @date) / (365.23076923074 * 24)
END

OR DAY

FUNCTION [dbo].[GetAge] (@birthday datetime, @date datetime)
RETURNS int
AS
BEGIN
return datediff(DAY, @birthday, @date) / (365.23076923074)
END
BateTech
  • 5,780
  • 3
  • 20
  • 31
0

Why not just DATEDIFF(year, @birthday, @date)? You only want the whole number of years, right?

pseudocoder
  • 4,314
  • 2
  • 25
  • 40
  • 1
    Because this doesn't work. DATEDIFF counts the number of date parts crossed. It does NOT caluclate the difference. Consider that the following returns 1, obviously this is only 1 day and not a year. select DATEDIFF(year, '2013-12-31', '2014-01-01') – Sean Lange Dec 17 '14 at 20:48
  • @SeanLange Thanks, I did not know that. Well I guess this question unsurprisingly has been answered many times already anyway...marking as duplicate. – pseudocoder Dec 17 '14 at 20:52
0

Try casting your birthday to a date before sending it off.

SELECT dbo.GetAge(birthday, select cast('1963-01-01 00:00:00.000' as date))
  FROM [dbo].[Users]
GO
user38858
  • 306
  • 4
  • 14