GETDATE in a function

I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
can't, because you can't put GETDATE in a function.
1) Why not?
2) What can I do to make this work?
MauryHi
Yes , you cannot , however you are be able to pass GETDATE() function as a
parameter onto the UDF and later to operate with it
> I'm trying to make three functions, Today, Yesterday and Tomorrow
SELECT
DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
DATEADD (d,DATEDIFF(D,0,GETDATE()),0) AS Today,
DATEADD (d,DATEDIFF(D,0,GETDATE())+1,0) AS Tomorrow
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7B10973B-0A65-4E45-848C-441ADB34E8F0@.microsoft.com...
> I'm trying to make three functions, Today, Yesterday and Tomorrow. But I
> can't, because you can't put GETDATE in a function.
> 1) Why not?
> 2) What can I do to make this work?
> Maury|||"Uri Dimant" wrote:
> Yes , you cannot
Any idea why? I can't seem to think of any good reason for this.
> however you are be able to pass GETDATE() function as a
> parameter onto the UDF and later to operate with it
UDF?
> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
Oh, I know how to do it mechanically, but it's precisely all that syntax
that I'm trying to avoid!
Maury|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>
Read this, it will explain all:
http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/|||When a function is executed, it has to know exactly what data it will
operate on. Getdate() is a 'non-determinate' function since every time it
operates, it produces a different value.
'UDF' = user defined function. A prefix that some use in naming functions.
Also in popular usage is 'fn'.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:15F3B36E-49DD-4539-8B24-1DBE13AA3CC7@.microsoft.com...
> "Uri Dimant" wrote:
>> Yes , you cannot
> Any idea why? I can't seem to think of any good reason for this.
>> however you are be able to pass GETDATE() function as a
>> parameter onto the UDF and later to operate with it
> UDF?
>> DATEADD (d,DATEDIFF(D,0,GETDATE()-1),0) AS Yesterday ,
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>|||"Arnie Rowland" wrote:
> When a function is executed, it has to know exactly what data it will
> operate on. Getdate() is a 'non-determinate' function since every time it
> operates, it produces a different value.
This doesn't really tell me WHY though. WHY does returning a different
datetime make it impossible to use in a function?
Consider this:
ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
BEGIN
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
This would be called like...
SELECT dbo.StartOfDay(GETDATE())
Now what is it about exactly that precudes this solution:
ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
BEGIN
SELECT @.date = GETDATE()
RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
'/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar, DAY(@.date))))
END
The end result is exactly the same. The code that needs to run is exactly
the same. The actual execution is almost identical. So what is it about the
function creation mechanism inside SQL Server that makes this illegal?
Maury|||In your first example, the function is NOT passed in the function getdate(),
is it passed in the current returned value from getdate() -so therefore, the
function knows exactly the values it has to operate with.
In your second example, the function would not know the value of getdate()
until after it starts working -and that is, by design, not allowed.
Why does 0/1 create such a turmoil in math. I don't know 'exactly, but I
accept the it just isn't allowed.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||User-defined functions are by definition "deterministic". A deterministic
function is one in which, for every value (or set of values) you pass into
it, it returns the same result. Examples of deterministic functions include
sine(), cosine(), etc.
Because of this limitation on determinism in user-defined functions, you are
not allowed to use non-deterministic functions inside your UDF's (like rand
or getdate). There are ways around this limitation, like creating a VIEW
composed of SELECT GETDATE(); and selecting from the VIEW from within your
UDF.
MS warns against relying on UDF's that circumvent determinism like this and
don't return the same results for the same set of values every time,
however.
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14D32701-04DF-4A86-B680-6C1669715DEF@.microsoft.com...
> "Arnie Rowland" wrote:
>> When a function is executed, it has to know exactly what data it will
>> operate on. Getdate() is a 'non-determinate' function since every time it
>> operates, it produces a different value.
> This doesn't really tell me WHY though. WHY does returning a different
> datetime make it impossible to use in a function?
> Consider this:
> ALTER FUNCTION dbo.StartOfDay(@.date datetime) RETURNS datetime
> BEGIN
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> This would be called like...
> SELECT dbo.StartOfDay(GETDATE())
> Now what is it about exactly that precudes this solution:
> ALTER FUNCTION dbo.StartOfDay() RETURNS datetime
> BEGIN
> SELECT @.date = GETDATE()
> RETURN convert(datetime, convert(varchar, convert(varchar, YEAR(@.date)) +
> '/' + convert(varchar, MONTH(@.date)) + '/' + convert(varchar,
> DAY(@.date))))
> END
> The end result is exactly the same. The code that needs to run is exactly
> the same. The actual execution is almost identical. So what is it about
> the
> function creation mechanism inside SQL Server that makes this illegal?
> Maury|||"Mike C#" wrote:
> Because of this limitation on determinism in user-defined functions
But why does this limitation exist? Is there a technical reason for this?
Everything I've seen so far says "you can't do it because we don't let you".
> or getdate). There are ways around this limitation, like creating a VIEW
> composed of SELECT GETDATE(); and selecting from the VIEW from within your
> UDF.
Which is baffling. If there is a real, technical, issue here, then this
shouldn't work either.
> MS warns against relying on UDF's that circumvent determinism like this and
> don't return the same results for the same set of values every time,
> however.
Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
document someone posted here, and done a few googles on it too, but every
single case states the fact, and not the reason.
It's all rather maddening.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:FA0444D2-F5B4-4CC2-820A-CB654C0455B0@.microsoft.com...
> "Mike C#" wrote:
> > Because of this limitation on determinism in user-defined functions
> But why does this limitation exist? Is there a technical reason for this?
> Everything I've seen so far says "you can't do it because we don't let
you".
One reason I SUSPECT is that in non-deterministic cases, things like
updates/inserts could cause wonkiness and violate ACID.
UPDATE FOO set entry_date=getdate()
All rows updated will have the exact same date/time, regardless of the
number of rows updated.
Now consider something like:
UPDATE BAR set entry_date=fn_mydate()
(assuming fn_mydate() basically is a user defined-function that returns
getdate() for simplicity's sake)
It may be harder for MS to guarantee that the optimizer only returns one
deterministic value for fn_mydate.
(worse case, every row gets a different date/time value.)
Now you've violated ACID.
This is just a guess on my part.
> > or getdate). There are ways around this limitation, like creating a
VIEW
> > composed of SELECT GETDATE(); and selecting from the VIEW from within
your
> > UDF.
> Which is baffling. If there is a real, technical, issue here, then this
> shouldn't work either.
> > MS warns against relying on UDF's that circumvent determinism like this
and
> > don't return the same results for the same set of values every time,
> > however.
> Do they give a reason anywhere? I've looked at the Transact-SQL docs, the
> document someone posted here, and done a few googles on it too, but every
> single case states the fact, and not the reason.
> It's all rather maddening.
> Maury