January 9, 2013 at 11:40 pm
Simple way to compute your age accurately.
A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).
In this query you exactly get the age as is it.
Example 1
DOB = 11/15/1987 and
datenow =11/15/2012 the result would be
AGE=25
Example 2
DOB = 11/16/1987 and
datenow =11/15/2012 the result would be
AGE=24
SO HERE ARE THE QUERY
DECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
HOPE I CAN HELP!
January 10, 2013 at 12:28 am
How about these solutions?
declare @DOB date = '20080229',
@CurDate date = '20130228';
select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;
set @CurDate = '20130301';
select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;
set @CurDate = '20130227';
select
@DOB as DateOfBirth,
@CurDate as CurrentDate,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, -datediff(yy, @DOB, @CurDate), @CurDate) < @DOB
then 1
else 0
end as TurnsYearOlderFirstOfMarch,
datediff(yy, @DOB, @CurDate) - case when dateadd(yy, datediff(yy, @DOB, @CurDate), @DOB) <= @CurDate
then 0
else 1
end as TurnsYearOlderLastOfFebruary;
January 10, 2013 at 8:25 am
How about this..............
DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '2008-02-29'
SET @STPR_START_DATE = '2013-02-28'
SELECT CASE WHEN YEAR(@STPR_START_DATE)%400 != 0 AND DAY(@STPR_START_DATE)= 28 THEN
DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + (DAY(@BIRTH_DATE) -1) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END
ELSE
DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END
END AS AGE
January 10, 2013 at 11:47 am
Michael Valentine Jones (1/9/2013)
Jeff Moden (1/9/2013)
So, which "standard" are you going to use for someone that is born on a leap year day? Feb 28th or Mar 1st? 😉
As usual, no one agrees on this, but different countries do at least have some standard.
http://en.wikipedia.org/wiki/February_29
"...in England and Wales or in Hong Kong, a person born on February 29, 1996, will have legally reached 18 years old on March 1, 2014. If he or she was born in the United States, Taiwan or New Zealand, he or she legally becomes 18 on February 28, 2014, a day earlier..."
I prefer the Feb 28 date for birthdays in non-leap years because that's the "standard" for the US. And, it's easier to code in TSQL.:-D
I hate to say it, but there is not actually a "standard" the U.S. - it depends on the precise purpose you're doing the date calculations for, and who needs them. Check with the business users about this, every time, particularly in regulated industries for for regulated/legal purposes.
January 10, 2013 at 7:04 pm
mdsharif532 (1/10/2013)
How about this..............DECLARE @BIRTH_DATE DATETIME, @STPR_START_DATE DATETIME
SET @BIRTH_DATE = '2008-02-29'
SET @STPR_START_DATE = '2013-02-28'
SELECT CASE WHEN YEAR(@STPR_START_DATE)%400 != 0 AND DAY(@STPR_START_DATE)= 28 THEN
DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + (DAY(@BIRTH_DATE) -1) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END
ELSE
DATEDIFF(YEAR, @BIRTH_DATE, @STPR_START_DATE) - CASE WHEN MONTH(@BIRTH_DATE)*100 + DAY(@BIRTH_DATE) > MONTH(@STPR_START_DATE)*100 + DAY(@STPR_START_DATE) THEN 1 ELSE 0 END
END AS AGE
A lot of extra work for what can be done easily with a couple of datetime functions and a case statement.
April 15, 2016 at 4:38 pm
USE [livedb];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetAge
(@DOB DATETIME,
@ToDate DATETIME
)
RETURNSSMALLINT
AS
BEGIN
DECLARE@Diff SMALLINT,
@F DATETIME,
@T DATETIME
SELECT@Diff = DATEDIFF(month, @DOB, @ToDate) / 12,
@F = DATEADD(year, 2000 - DATEPART(year, @DOB), @DOB),
@T = DATEADD(year, 2000 - DATEPART(year, @ToDate), @ToDate)
IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0
BEGIN
IF @DOB <= @ToDate AND @F > @T
SELECT@Diff = @Diff - 1
IF @DOB > @ToDate AND @F < @T
SELECT@Diff = @Diff + 1
END
RETURN@Diff
END
April 15, 2016 at 7:22 pm
hrothenb (4/15/2016)
USE [livedb];GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetAge
(@DOB DATETIME,
@ToDate DATETIME
)
RETURNSSMALLINT
AS
BEGIN
DECLARE@Diff SMALLINT,
@F DATETIME,
@T DATETIME
SELECT@Diff = DATEDIFF(month, @DOB, @ToDate) / 12,
@F = DATEADD(year, 2000 - DATEPART(year, @DOB), @DOB),
@T = DATEADD(year, 2000 - DATEPART(year, @ToDate), @ToDate)
IF DATEDIFF(month, @DOB, @ToDate) % 12 = 0
BEGIN
IF @DOB <= @ToDate AND @F > @T
SELECT@Diff = @Diff - 1
IF @DOB > @ToDate AND @F < @T
SELECT@Diff = @Diff + 1
END
RETURN@Diff
END
It doesn't need to be that complicated.
CREATE FUNCTION dbo.AgeInYears
(
@StartDT DATETIME, --Date of birth or date of manufacture or start date.
@EndDT DATETIME --Usually, GETDATE() or CURRENT_TIMESTAMP but
--can be any date source like a column that has an end date.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT AgeInYears =
DATEDIFF(yy, @StartDT, @EndDT)
- CASE --If anniversary date hasn't happened yet this year, subtract 1.
WHEN DATEADD(yy, DATEDIFF(yy, @StartDT, @EndDT), @StartDT) > @EndDT
THEN 1
ELSE 0
END
;
The above is a pseudo iSF (Inline Scalar Function), as well. They'll typically beat regular scalar functions by quite a bit. Please see the following article on that subject.
http://www.sqlservercentral.com/articles/T-SQL/91724/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2016 at 11:32 am
As always, please be sure to know your requirements, and whether your particular calculation considers a Leap Day (Feb 29 birthdate) baby to have their birthday on Feb 28, or on Mar 1, of non-leap-years.
Note that this can be a purpose-defined field; for instance, for certain uses, one national government agency may say it must be Feb 28, and for other uses, a particular regional government may say it must be Mar 1.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply