• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

How to Simulate GETDATE in a User Defined Function

My function needs to return a year value from a table. If that value does not exist, it needs to return the current year.

This is my function. I've commented out the part about the current year because SQL Server won't let me use GETDATE inside a function. How can I get around this?

CREATE FUNCTION [dbo].[udfGovernYear]
      (
      )  
RETURNS INT AS
 
BEGIN
--------------------------------------------
-- local variables
--------------------------------------------
DECLARE @Return INT
--------------------------------------------
-- get the value
--------------------------------------------
SELECT       TOP 1 @Return = CURRENT_FY
FROM       dbo.USR_DEPARTMENT
ORDER BY   DEPT
--------------------------------------------
-- handle missing data
--------------------------------------------
/*
IF @Return IS NULL BEGIN
     SET @Return = YEAR(GETDATE())
END
*/
--------------------------------------------
-- done
--------------------------------------------
RETURN @Return
END
0
rmk
Asked:
rmk
1 Solution
 
ColosseoCommented:
Hi

I had this problem a while back and what I ended up doing was passing getdate as an arguement to the function and then using the variable inside the function.

Let me know if you need help getting it going with an example or help with your code

Scott
0
 
ColosseoCommented:
So using your function you could have

CREATE FUNCTION [dbo].[udfGovernYear] (@currentDate datetime)
     (
     )  
RETURNS INT AS
 
BEGIN
--------------------------------------------
-- local variables
--------------------------------------------
DECLARE @Return INT
--------------------------------------------
-- get the value
--------------------------------------------
SELECT      TOP 1 @Return = CURRENT_FY
FROM      dbo.USR_DEPARTMENT
ORDER BY   DEPT
--------------------------------------------
-- handle missing data
--------------------------------------------

IF @Return IS NULL BEGIN
     SET @Return = YEAR(@CurrentDate)
END

--------------------------------------------
-- done
--------------------------------------------
RETURN @Return
END

and you would just call it using  dbo.udfGovernYear(GETDATE())

Scott
0
 
HilaireCommented:
Here's an option

create view v_dummy as select getdate() as currdate
go

CREATE FUNCTION [dbo].[udfGovernYear] (@currentDate datetime)
     (
     )  
RETURNS INT AS
 
BEGIN
--------------------------------------------
-- local variables
--------------------------------------------
DECLARE @Return INT
--------------------------------------------
-- get the value
--------------------------------------------
SELECT      TOP 1 @Return = CURRENT_FY
FROM      dbo.USR_DEPARTMENT
ORDER BY   DEPT
--------------------------------------------
-- handle missing data
--------------------------------------------

IF @Return IS NULL BEGIN
     SELECT @Return = YEAR(CurrDate) from v_dummy
END

--------------------------------------------
-- done
--------------------------------------------
RETURN @Return
END
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HilaireCommented:
Oops sorry the function should not have a parameter

CREATE FUNCTION [dbo].[udfGovernYear]()
RETURNS INT AS
BEGIN
--------------------------------------------
-- local variables
--------------------------------------------
DECLARE @Return INT
--------------------------------------------
-- get the value
--------------------------------------------
SELECT      TOP 1 @Return = CURRENT_FY
FROM      dbo.USR_DEPARTMENT
ORDER BY   DEPT
--------------------------------------------
-- handle missing data
--------------------------------------------

IF @Return IS NULL BEGIN
     SELECT @Return = YEAR(CurrDate) from v_dummy
END

--------------------------------------------
-- done
--------------------------------------------
RETURN @Return
END
0
 
rmkAuthor Commented:
Hilaire - that's a cool answer.

I added a view called vwNow which does a SELECT GETDATE() as Now and then used SELECT @Return Year(Now) FROM vwNow
0
 
HilaireCommented:
Here's a less verbose version of your code

CREATE FUNCTION [dbo].[udfGovernYear]()
RETURNS INT AS
BEGIN
RETURN COALESCE(
      (SELECT TOP 1 CURRENT_FY FROM dbo.USR_DEPARTMENT ORDER BY DEPT),
      (SELECT Year(Now) FROM vwNow)
)
END
0
 
roshkmCommented:
Use stored procedure..   :)

Regards,
RKM
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now