CODEWITHSUNDEEP
sql-serverdate-arithmetic
Amehiny
Amehiny

Reputation: 135

Calculating the Age from the Birthday function in SQL

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

Upvotes: 0

Views: 1985

Answers (5)

user38858
user38858

Reputation: 316

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

Upvotes: 0

pseudocoder
pseudocoder

Reputation: 4402

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

Upvotes: 0

BateTech
BateTech

Reputation: 6526

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

Upvotes: 0

Spock
Spock

Reputation: 4910

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)

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 0

Related Questions