While writing few articles about SQL Server DataTime I accidentally wrote User Defined Function (UDF), which I would have not wrote usually. Once I wrote this function, I did not find it very interesting and decided to discard it. However, I suddenly noticed use of Non-Deterministic function in the UDF.
I always thought that use of Non-Deterministic function is prohibited in UDF. I even wrote about it earlier SQL SERVER – User Defined Functions (UDF) Limitations. It seems like SQL Server 2005 either have removed this restriction or it is bug. I think I will not say this is bug but I will categorized it as feature.
GETDATE() is Non-Deterministic function and it can be used in User Defined Function in SQL Server 2005.
T-SQL Script to create UDF:
CREATE FUNCTION dbo.udf_DateDifference (@dtBeginDate datetime)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @rtMessage VARCHAR(100)
SET @rtMessage = 'Different between ' + CAST(@dtBeginDate AS VARCHAR(11)) + ' and ' +
CAST(GETDATE() AS VARCHAR(11)) + 'is' +
CAST(DATEDIFF(DAY,@dtBeginDate,GETDATE()) AS VARCHAR(11))
RETURN @rtMessage
END
GO
Execute above function running following script:
SELECT dbo.udf_DateDifference('8/1/2007') AS DayDifference
ResultSet:
DayDifference
—————————————————————————————————-
Different between Aug 1 2007 and Aug 23 2007 is 23
Reference : Pinal Dave (https://blog.sqlauthority.com)
