Solved

Problem using GETDATE() in UDF function

Posted on 2006-07-04
7
398 Views
Last Modified: 2012-05-05
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
Comment
Question by:javilmer
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17037924
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
 
LVL 3

Expert Comment

by:mte01
ID: 17037927
>>javilmer

Use:
SET @iResult = 10000 * day(GETDATE())
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 17037929
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17037941
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
 

Author Comment

by:javilmer
ID: 17037972
yes, we added dbo.GETDATE() because a simple GETDATE leads to a syntax error
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17037975
Check my previous post and 'LowfatSpread's post'
0
 
LVL 4

Expert Comment

by:kenpem
ID: 17038096
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now