getdate in UDF

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)

w00kAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
nmcdermaidCommented:
You can pass GETDATE() it in as an argument but that kind of defeats the purpose hey.
0
 
w00kAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.