Seems you it is impossible to use GETDATE() to get the current date in MSSQL user defined function because of determinism, i.e. GETDATE() will always return different values. However it is possible to get the desired functionality with the help of the following little trick. We can create a view and use GETDATE() in a view. Later our user defined function will query this view.
CREATE VIEW V_DATE AS SELECT GETDATE() VAL
user defined function will look like so:
SELECT VAL FROM V_DATE
Thursday, December 16, 2004 10:32 PM