February 23, 2016 at 2:49 am
C# Gnu (3/13/2009)
Richard Briggs (3/13/2009)
Comrades,as regards my own post on creating a CALCULATED PERSISTED column containing 'Age'.
I have discovered that actually because GetDate() is non-deterministic such a column cannot be made to PERSISTED.
It is the fact that it is Persisted that would give me the performance benefit I would demand.
Therefore I now have a new column Age im my Person table that is re-calulated by an SQL Server job, this runs shortly after midnight - using an UPDATE statement to recalcualte the Age for all records in my Person database.
Now that I have this column (remember I am talking BIG database here many millions rows) I am able to index the column and my Query (WHERE Age > 50) is v.fast!;)
Brigzy
And Comrades I use :
-- ==================================================-- Author:R.Briggs
-- Create date: 13/3/09
-- Description:Return age from DOB
--
-- Age today:
-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person
--
-- Age on Christmas day:
-- SELECT fn_GetAge (DateOfBirth, '25 December 2009 ') FROM Person
--
-- ==================================================
CREATE FUNCTION [dbo].[fn_GetAge] ( @pDateOfBirth DATETIME,
@pAsOfDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @intAge INT
IF @pDateOfBirth >= @pAsOfDate
RETURN 0
SET @intAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)
IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @intAge = @intAge - 1
RETURN @intAge
END
If you think its wrong - Please let me know!
Brigzy
Old thread awoken here but it was a good hit I got when searching on the issue.
Would this not be faster? It's your code but modified so the performance hit of a scalar function should not interfere.
CREATE FUNCTION [dbo].[GetAge]
(
@DateOfBirth DATETIME,
@AsOfDate DATETIME
)
RETURNS TABLE
AS
RETURN
(
select case when @DateOfBirth >= @AsOfDate then 0
when MONTH(@DateOfBirth) > MONTH(@AsOfDate)
OR (MONTH(@DateOfBirth) = MONTH(@AsOfDate)
AND DAY(@DateOfBirth) > DAY(@AsOfDate))
then DATEDIFF(YY, @DateOfBirth, @AsOfDate) -1
else DATEDIFF(YY, @DateOfBirth, @AsOfDate)
end
as Age
)
GO
February 24, 2016 at 12:19 am
Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.
Thanks a mil.:-D:-D:-D:-D:-D
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 24, 2016 at 8:54 am
Manie Verster (2/24/2016)
Thank you Richard, you have just solved a problem for me of an old problem of giving a valid age. Yes, I know there are most probably many solutions out there but the problem has not been serious enough to actually do something about it and Lynn also had a very good solution in her article but you script just put the cherry on the cake.Thanks a mil.:-D:-D:-D:-D:-D
I just have to correct you, I am a guy.
March 15, 2016 at 12:54 am
Sorry Lynn, my bad!:blush:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
October 1, 2024 at 4:32 pm
DECLARE @DOB DATETIME, @CurrDT DATETIME, @YY INT, @mm INT, @DD INT
SET @CurrDT = GETDATE()
SET @DOB = '1984-11-12'
SET @YY = YEAR(@CurrDT) - YEAR(@DOB)
SET @mm = MONTH(@CurrDT) - MONTH(@DOB)
SET @DD = DAY(@CurrDT) - DAY(@DOB)
IF(@DD < 0)
BEGIN
SET @DD = @DD + 30;
END
IF(@MM < 0)
BEGIN
SET @YY = @YY - 1
END
SELECT CONVERT(VARCHAR(50),@YY) + ' Y, ' + CONVERT(VARCHAR(50),@MM) + ' M, ' + CONVERT(VARCHAR(50),@DD) + ' D'
October 1, 2024 at 4:43 pm
DECLARE @DOB DATETIME, @CurrDT DATETIME, @YY INT, @mm INT, @DD INT
SET @CurrDT = GETDATE()
SET @DOB = CONVERT(DATETIME,@Filter1,101)--'1984-11-12'
SET @YY = YEAR(@CurrDT) - YEAR(@DOB)
SET @mm = MONTH(@CurrDT) - MONTH(@DOB)
SET @DD = DAY(@CurrDT) - DAY(@DOB)
IF(@DD < 0)
BEGIN
SET @DD = @DD + 30;
END
IF(@MM < 0)
BEGIN
SET @YY = @YY - 1
END
SELECT= CONVERT(VARCHAR(50),@YY) + ' Y, ' + CONVERT(VARCHAR(50),@MM) + ' M, ' + CONVERT(VARCHAR(50),@DD) + ' D'
Viewing 6 posts - 91 through 95 (of 95 total)
You must be logged in to reply to this topic. Login to reply