Solved

Problem using GETDATE() in UDF function

Posted on 2006-07-04
7
390 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
Comment Utility
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
Comment Utility
>>javilmer

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

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
yes, we added dbo.GETDATE() because a simple GETDATE leads to a syntax error
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Check my previous post and 'LowfatSpread's post'
0
 
LVL 4

Expert Comment

by:kenpem
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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
Viewers will learn how the fundamental information of how to create a table.

743 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

16 Experts available now in Live!

Get 1:1 Help Now