[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Problem using GETDATE() in UDF function

Hello,

We need a UDF function that send the following :

CREATE function dbo.osintNow()
RETURNS int
AS
BEGIN

DECLARE @iResult int

SET @iResult = 10000 * day(dbo.GETDATE())

RETURN @iResult
END

We can create it correctly but when we want to use it, we get :

Invalid object name 'dbo.GETDATE'.

Why ???
0
javilmer
Asked:
javilmer
1 Solution
 
LowfatspreadCommented:
try ...
basically getdate() doesn't have a dbo... its a system function...

CREATE function dbo.osintNow()
RETURNS int
AS
BEGIN

DECLARE @iResult int

SET @iResult = 10000 * day(GETDATE())

RETURN @iResult
END
0
 
mte01Commented:
>>javilmer

Use:
SET @iResult = 10000 * day(GETDATE())
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi javilmer,

You can't directly use GETDATE() inside a function, the better otpion is to pass it as an argument

CREATE function dbo.osintNow(
@today smalldatetime
)
RETURNS int
AS
BEGIN

DECLARE @iResult int

SET @iResult = 10000 * day(@today)

RETURN @iResult
END

GO

DECLARE @today smalldatetime
SET @Today = Getdate()
SELECT dbo.osintNow(@today)



Aneesh R!
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LowfatspreadCommented:
you may find it appropriate to

create view Today
 as
  Select Getdate() as Now
           ,Day(getdate() as DayNumber
           
go

and use the view in your queries instead...

 
0
 
javilmerAuthor Commented:
yes, we added dbo.GETDATE() because a simple GETDATE leads to a syntax error
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Check my previous post and 'LowfatSpread's post'
0
 
kenpemCommented:
SQL will not allow you to use GETDATE in a user-defined function.

I bumped heads with this one for ages, before finally coming up with this:

CREATE FUNCTION [dbo].[Now]() RETURNS DATETIME AS
BEGIN
    DECLARE @dt DATETIME
    SELECT @dt = dt  FROM OPENQUERY ( ***SERVERNAME***,  'SELECT dt = GETDATE()' )
    RETURN @dt
END

Now whenever you need the current timestamp within a function, use "dbo.Now()" instead of "GETDATE()".
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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