GETDATE in a function

Hi
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:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> 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/i.../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:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> 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:
>
> 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:
>
> 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|||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|||Maury Markowitz wrote:
> "Uri Dimant" wrote:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> 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/i.../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:
>
> Any idea why? I can't seem to think of any good reason for this.
>
> UDF?
>
> Oh, I know how to do it mechanically, but it's precisely all that syntax
> that I'm trying to avoid!
> Maury
>