How to calculate datetime udf function problems

Question

Hi bros and sis Please help me I have problems for sql database date time calculation udf function.This udf function is do not refresh table calculation is true.But table refreshed not true value. Please help me and advice.

Nitin Sontakke · Answer

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.

U Thu · Answer

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

Nitin Sontakke · Answer

Looks like you cannot do it. Please carefully read this thread: http://stackoverflow.com/questions/1744455/using-udf-for-default-value-of-a-column The alternative is to create a function as follows: CREATE FUNCTION [dbo].[TotalYearsMonthsDays] --this is function name ( @ID [ INTEGER ] ) 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 --Assign Value from database 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 GO And use it as follows: SELECT *, [dbo].[TotalYearsMonthsDays]([ID]) FROM [dbo].[Personal] Hope it helps!

U Thu · Answer

our database table is 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

Nitin Sontakke · Answer

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: select @givenDate = [Birthday] from [dbo].[Personal] where [Id] = @Id; As I had said before I have never used it like this

U Thu · Answer

My Database table and UDF Function code is

U Thu · Answer

My Database table and UDF Function code is

Nitin Sontakke · Answer

Why not share the code? Otherwise how will we know? May be the WHERE clause is missing.