?
Solved

getdate in UDF

Posted on 2006-04-27
5
Medium Priority
?
542 Views
Last Modified: 2012-06-22
I'm having some problems with getting today's date into a udf.  I've read that you can not use getdate inside a udf b/c it is non-deterministic.  So what are my options?

In the where clause of my udf I've got:

mytable.mydatetimefield>=CONVERT(CHAR(8), GETDATE(), 112)

0
Comment
Question by:w00k
5 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 16557738
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16558064
You can pass GETDATE() it in as an argument but that kind of defeats the purpose hey.
0
 

Author Comment

by:w00k
ID: 16558074
Maybe I should just post the function and have you all just take a look.  The part with getdate in the where clause is where I'm having a difficult problem with:

CREATE Function [dbo].[udf_GetTrkBookingCheckPoints] (@book_urn INTEGER,@checkpoint_urn INTEGER)

RETURNS DATETIME

AS

BEGIN

DECLARE @CPDT DATETIME

SELECT  @CPDT = testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime

FROM                     booking WITH (NOLOCK)
LEFT OUTER JOIN      testmedtrack.dbo.trk_booking WITH (NOLOCK) ON booking.book_urn = testmedtrack.dbo.trk_booking.or_book_urn
LEFT OUTER JOIN      testmedtrack.dbo.trk_booking_checkpoint WITH (NOLOCK) ON testmedtrack.dbo.trk_booking.book_urn = testmedtrack.dbo.trk_booking_checkpoint.book_urn

WHERE     booking.book_urn = @book_urn AND testmedtrack.dbo.trk_booking_checkpoint.checkpoint_urn = @checkpoint_urn
AND testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime >=CONVERT(CHAR(8), GETDATE(), 112)

GROUP BY       dbo.booking.book_urn,
            testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime
RETURN @CPDT

END
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 total points
ID: 16558674
As nmcdermaid has suggested (no points please) modify your UDF as follows:
CREATE Function [dbo].[udf_GetTrkBookingCheckPoints] (@book_urn INTEGER,@checkpoint_urn INTEGER, @satisfied_datetime datetime)

RETURNS DATETIME

AS

BEGIN

DECLARE @CPDT DATETIME

SELECT  @CPDT = testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime

FROM                   booking WITH (NOLOCK)
LEFT OUTER JOIN     testmedtrack.dbo.trk_booking WITH (NOLOCK) ON booking.book_urn = testmedtrack.dbo.trk_booking.or_book_urn
LEFT OUTER JOIN     testmedtrack.dbo.trk_booking_checkpoint WITH (NOLOCK) ON testmedtrack.dbo.trk_booking.book_urn = testmedtrack.dbo.trk_booking_checkpoint.book_urn

WHERE     booking.book_urn = @book_urn AND testmedtrack.dbo.trk_booking_checkpoint.checkpoint_urn = @checkpoint_urn
AND testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime >=@satisfied_datetime

GROUP BY      dbo.booking.book_urn,
          testmedtrack.dbo.trk_booking_checkpoint.satisfied_datetime
RETURN @CPDT

END

Then call it as follows:
Selec [dbo].[udf_GetTrkBookingCheckPoints] (@book_urn, @checkpoint_urn, CONVERT(CHAR(8), GETDATE(), 112))
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16558960
2 more options

1. Take date from sysprocess table

use the following inside the function

declare @date datetime
select @date =max(last_batch) from sysprocesses

2. Create a view and use it
CREATE VIEW Now
AS select getdate () RightNow
go

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

809 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