Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Industry Leaders: 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