How to calculate datetime udf function problems

It is happening like that because your function is called BEFORE the record is even actually inserted.

 

That's why you cannot do it the way you want to do it. Please read the link from my post.

 

You can use the alternative suggestion given by me.

i change look like this

CREATE FUNCTION [dbo].[TotalYearsMonthsDays] --this is function name
(

)
RETURNS VARCHAR(50) --this is return result type
AS
BEGIN
--declear variable
DECLARE @givenDate datetime
DECLARE @tempDate datetime
DECLARE @years int, @months int, @days int,@ID int
--Assign Value from database
SELECT @ID = ID from dbo.Personal
SELECT @givenDate = Birthday from dbo.Personal where ID = @ID
SELECT @tempDate = @givenDate
-- get year claculation
SELECT @years = DATEDIFF(yy, @tempDate, GETDATE()) -
CASE
WHEN
(MONTH(@givenDate) > MONTH(GETDATE()))
OR
(MONTH(@givenDate) = MONTH(GETDATE())
AND
DAY(@givenDate) > DAY(GETDATE()))
THEN 1
ELSE 0
END
SELECT @tempDate = DATEADD(yy, @years, @tempDate)

-- get months calculation
SELECT @months = DATEDIFF(m, @tempDate, GETDATE()) -
CASE
WHEN
DAY(@givenDate) > DAY(GETDATE())
THEN 1
ELSE 0
END
SELECT @tempDate = DATEADD(m, @months, @tempDate)

-- get days calculation
SELECT @days = DATEDIFF(d, @tempDate, GETDATE())

--print out result
RETURN (SELECT CAST(@years as varchar(5)) + ' years ' +
CAST(@months as varchar(3)) + ' months ' +
CAST(@days as varchar(3)) + ' days' )
END 

 

table refresh ok look like see in figure how to change database table

Looks like you cannot do it. Please carefully read this thread:

 

 

The alternative is to create a function as follows:

 

And use it as follows:

 

Hope it helps!

CREATE TABLE [dbo].[Personal] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[PersonalID] NVARCHAR (50) NULL,
[Name] NVARCHAR (50) NULL,
[Birthday] DATETIME NULL,
[TEST] NCHAR (10) DEFAULT ([dbo].[TotalYearsMonthsDays]()) NULL,
PRIMARY KEY CLUSTERED ([ID] ASC)
);


MY UDF Function is 

CREATE FUNCTION [dbo].[TotalYearsMonthsDays] --this is function name
(

)
RETURNS VARCHAR(50) --this is return result type
AS
BEGIN
--declear variable
DECLARE @givenDate datetime
DECLARE @tempDate datetime
DECLARE @years int, @months int, @days int,@ID int
--Assign Value from database

SELECT @givenDate = Birthday from dbo.Personal
SELECT @tempDate = @givenDate
-- get year claculation
SELECT @years = DATEDIFF(yy, @tempDate, GETDATE()) -
CASE
WHEN
(MONTH(@givenDate) > MONTH(GETDATE()))
OR
(MONTH(@givenDate) = MONTH(GETDATE())
AND
DAY(@givenDate) > DAY(GETDATE()))
THEN 1
ELSE 0
END
SELECT @tempDate = DATEADD(yy, @years, @tempDate)

-- get months calculation
SELECT @months = DATEDIFF(m, @tempDate, GETDATE()) -
CASE
WHEN
DAY(@givenDate) > DAY(GETDATE())
THEN 1
ELSE 0
END
SELECT @tempDate = DATEADD(m, @months, @tempDate)

-- get days calculation
SELECT @days = DATEDIFF(d, @tempDate, GETDATE())

--print out result
RETURN (SELECT CAST(@years as varchar(5)) + ' years ' +
CAST(@months as varchar(3)) + ' months ' +
CAST(@days as varchar(3)) + ' days' )
END 

I have never used function like this. However, just as I had expected, there is no WHERE clause while picking up the date from the table.

 

I suggest you modify the function to accept the Id parameter and send current row's Id as a parameter.

 

Then modify the line as follows:

 

 

As I had said before I have never used it like this  

Why not share the code? Otherwise how will we know?

 

May be the WHERE clause is missing.