April 8, 2018 at 10:23 am
Comments posted to this topic are about the item Avoiding Unexpected Results with DATEDIFF in Year Comparisons
April 8, 2018 at 7:45 pm
Here is a way to make the calculation:
declare @testdate datetime = '2013-10-24';
select
getdate() as Today
, @testdate as DueDate
, datediff(year,@testdate,getdate()) -
case when getdate() < dateadd(year,datediff(year,@testdate,getdate()),@testdate)
then 1
else 0
end as DiffInYears;
go
April 8, 2018 at 8:29 pm
@dcs1548 ,
Dividing by 365 is far from accurate, as well, and there's no reason to suffer such inaccuracies especially on date ranges that include one or more leap years.
CREATE FUNCTION dbo.AgeInYears
/**********************************************************************************
Purpose:
Given two dates in expected order, calculate the difference in the dates by years.
-----------------------------------------------------------------------------------
Programmers Notes:
1. This is a high performance iTVF (Inline Table Valued Function), which is faster
than an equivalent Scalar Function and just as fast as inline code. Please
refer to the following link for proof of that.
http://www.sqlservercentral.com/articles/T-SQL/91724/
2. When the output is dumped to a variable to take disk and screen performance
out of the picture, it executes a million row table of two dates in ~820ms.
-----------------------------------------------------------------------------------
Usage Examples:
--===== Basic syntax
SELECT AgeInYears
FROM dbo.AgeInYears(@pLoDate,@pHiDate)
;
--===== Use against a table where the end date may be NULL
SELECT AgeInYears
FROM dbo.SomeTable st
CROSS APPLY dbo.AgeInYears(st.SomeDate1,ISNULL(st.SomeDate2,GETDATE())
;
-----------------------------------------------------------------------------------
Revision History:
Rev 00 - 11 Mar 2017 - Group effort by the folks at the following link.
- https://www.sqlservercentral.com/Forums/1863521/
- Formalized code - Jeff Moden
**********************************************************************************/
--===== Declare the I/O for this function
(--======= These dates must be in the expected order.
@pLoDate DATE
,@pHiDate DATE
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT AgeInYears =
DATEDIFF(yy,@pLoDate,@pHiDate)
- CASE
WHEN CONVERT(CHAR(5),@pLoDate,1) > CONVERT(CHAR(5),@pHiDate,1)
THEN 1
ELSE 0
END
;
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 4:08 am
There aren't 365 days in a year. Seeing that hard-coded constant in the code raised about seventeen code-smells in my mind
April 9, 2018 at 4:24 am
I use this code to work out AgeDECLARE @DateOfBirth DATETIME = '1980-07-12';
SELECT
DATEDIFF(YEAR, @DateOfBirth, GETDATE())
- CASE
WHEN (MONTH(@DateOfBirth) > MONTH(GETDATE()))
OR (MONTH(@DateOfBirth) = MONTH(GETDATE()) AND DAY(@DateOfBirth) > DAY (GETDATE()))
THEN 1
ELSE 0
END AS Age
or I use this function if being used in a wider project
IF object_id(N'[dbo].[udf_CalculateAge]', 'Fn') IS NOT NULL
DROP FUNCTION [DBO].[udf_CalculateAge]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION udf_CalculateAge(@dateofbirth AS DATE, @clientid AS INT) RETURNS INT
AS
BEGIN
DECLARE @output AS INT
SELECT @output=
DATEDIFF(yyyy, @dateofbirth, GETDATE())
- CASE
WHEN MONTH(@dateofbirth) > MONTH(GETDATE()) THEN 1
WHEN MONTH(@dateofbirth) = MONTH(GETDATE()) AND DAY(@dateofbirth) > DAY(GETDATE()) THEN 1
ELSE 0
END
FROM Clients
WHERE ClientID = @clientid
RETURN @output;
END
GO
Regards
Steve
April 9, 2018 at 5:13 am
DaveBoltman - Monday, April 9, 2018 4:08 AMThere aren't 365 days in a year. Seeing that hard-coded constant in the code raised about seventeen code-smells in my mind
You should post what they are along with some code to resolve the issue. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 5:32 am
On a different nit-pick to what has already been discussed, it's helpful to post your code as text, not as an image. Other users can't highlight the text within your image and use it, thus they would have to type it all back out. For a small script as this, that's not awful, however, for longer code that's going to drive people away from using your solution (or have them type it out incorrectly).
Of course, however, this is a kind of mute point when several others have pointed out a limitation/flaw of the script. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 9, 2018 at 6:05 am
I recall back in my engineering school days we always used 365.25 to account for leap year.
SQLOzzie
April 9, 2018 at 6:43 am
For fun, here is a function that calculates the number of days in a year (leap year or normal year)
😎
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.ITVFN_GET_DAYS_IN_YEAR
---------------------------------------------------------------------
-- Calculate the number of days in a year
-- The function is based on the common leap year algorithm
---------------------------------------------------------------------
-- if (year is not divisible by 4) then (it is a common year)
-- else if (year is not divisible by 100) then (it is a leap year)
-- else if (year is not divisible by 400) then (it is a common year)
-- else (it is a leap year)
-- © Wikipedia
-- https://en.wikipedia.org/wiki/Leap_year
---------------------------------------------------------------------
(
@INPUT_DATE DATETIME
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH THE_YEAR AS
(
SELECT YEAR(@INPUT_DATE) AS YEAR_NUM
)
SELECT
365 + SIGN( 1 - SIGN(((TY.YEAR_NUM) % 4 )) + (SIGN(((TY.YEAR_NUM) % 100)) - SIGN(((TY.YEAR_NUM) % 400)))) AS DAYS_IN_YEAR
FROM THE_YEAR TY;
GO
April 9, 2018 at 7:38 am
So the coping strategies are
1) A function such as (advantage - it will be correct - disadvantage - will be slow if needed for lots of data)
CREATE FUNCTION [dbo].[UDF_CalculateAge]
(
@BirthDate DATETIME,
@CurrentDate DATETIME
)
RETURNS INT
AS
BEGIN
IF @BirthDate > @CurrentDate
RETURN 0
DECLARE @Age INT
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END
RETURN @Age
END
2) Do a datediff in days and divide the result by 365.25 (advantage - it will be quick , disadvantage - it will sometimes be a little bit wrong but generally only for less than a day - this might be good enough)
3) It is sometimes appropriate to do a dateadd instead of datediff eg
SELECT top 10 DATE_OF_BIRTH,
CASE WHEN DATEADD(yy,18,DATE_OF_BIRTH) > GETDATE() THEN 'Child'
ELSE 'Adult'
END AS AdultorChild
FROM dimpatient
April 9, 2018 at 8:29 am
What I think is important to note about the author's post is this: When you do your own mathematical manipulation of datetime data, you are probably--almost certainly--making a mistake. A datetime is a data structure, not a numeral. For all date calculations, I highly advise using only functions that are provided for the datatype. For example, note Jeff's AgeInYears function, which uses DateDiff and Convert.
April 9, 2018 at 9:02 am
You would actually want to divide by 365.25, not 365.0, if you want a more accurate year difference.
April 9, 2018 at 11:31 am
zechstarks - Monday, April 9, 2018 9:02 AMYou would actually want to divide by 365.25, not 365.0, if you want a more accurate year difference.
Gosh, no. Don't use division for this. The alternatives are easy and guaranteed to be accurate.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 11:33 am
trevor.adams - Monday, April 9, 2018 7:38 AMSo the coping strategies are1) A function such as (advantage - it will be correct - disadvantage - will be slow if needed for lots of data)
CREATE FUNCTION [dbo].[UDF_CalculateAge]
(
@BirthDate DATETIME,
@CurrentDate DATETIME
)
RETURNS INT
AS
BEGIN
IF @BirthDate > @CurrentDate
RETURN 0
DECLARE @Age INT
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) - CASE WHEN( (MONTH(@BirthDate)*100 + DAY(@BirthDate)) > (MONTH(@CurrentDate)*100 + DAY(@CurrentDate)) ) THEN 1 ELSE 0 END
RETURN @Age
END
2) Do a datediff in days and divide the result by 365.25 (advantage - it will be quick , disadvantage - it will sometimes be a little bit wrong but generally only for less than a day - this might be good enough)
3) It is sometimes appropriate to do a dateadd instead of datediff egSELECT top 10 DATE_OF_BIRTH,
CASE WHEN DATEADD(yy,18,DATE_OF_BIRTH) > GETDATE() THEN 'Child'
ELSE 'Adult'
END AS AdultorChild
FROM dimpatient
"Good enough" usually isn't, even if someone thinks so. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2018 at 12:11 pm
Here's mine...CREATE FUNCTION YearsDiff(@Born date,@AsOf date) RETURNS int AS
BEGIN
DECLARE @y int
SET @y = DATEDIFF(YEAR,@Born,@AsOf)
IF DATEADD(year,@y,@Born) > @AsOf SET @y -= 1
RETURN @y
END
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply