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(http://www.SQLAuthority.com)
pinaldave
SQL Server 2005 has allowed some of the non-deterministic functions that were not allowed in 2000. In 2005 you can now use
CURRENT_TIMESTAMP
@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUS
@@PACK_RECEIVED
GETDATE
@@PACK_SENT
GETUTCDATE
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE and
@@IO_BUSY
You still cannot use
NEWID
RAND
NEWSEQUENTIALID and
TEXTPTR
I got that detail from SQL Server Magazine, and may have changed since publication, but the end result is - it is not a bug, you can use GETDATE() in functions,
Here is more directly from the article
“SQL Server 2005 Books Online (BOL) doesn’t provide a reason for this change or the distinction between the functions that are and aren’t allowed, but you’ll notice that the functions you can’t use have side effects. That is, the SQL Server 2005 non-deterministic functions that aren’t allowed make internal changes in the database or the system and leave a mark (such as a change to an interval value) that they have been invoked. And UDFs aren’t allowed to have side effects. For example, when SQL Server invokes the RAND function it changes an internal value that’s used to calculate the seed for the next invocation of the function. This means that had SQL Server allowed you to invoke RAND within a UDF, the invocation of the UDF would have changed the result of subsequent invocations of RAND outside the UDF as well. For this reason, you can’t use RAND in a UDF, even in SQL Server 2005.”
So in other words, we’re not able to do things we should logically be able to do because of poor design in SQL Server’s internals? Yes, heaven forbid a UDF use Rand(), and make subsequent calls to Rand() non-deterministic…
Someone as M$ is taking the whole “no side-effects” thing a bit too literally. At least this is a step in the right direction.
[...] SQL SERVER - 2005 - Use of Non-deterministic Function in UDF - Find Day Difference Between Any Date … You can run following code to determine if function is deterministic or not. [...]
how to change only day part with the day what we want in the date?
for example,i need to compute the date(6-dec-08) which is having the day(’6′ should be changed) part from a table.