Why can't I use GETDATE in a function?

  • Thread starter Maury Markowitz
  • Start date
M

Maury Markowitz

I would like to make a function called Today that returns the start of the
day. I can't, because SQL Server doesn't appear to allow GETDATE in a
function. I can't imagine why this would be. Anyone know?

Maury
 
M

Mike Labosh

I would like to make a function called Today that returns the start of the
day. I can't, because SQL Server doesn't appear to allow GETDATE in a
function. I can't imagine why this would be. Anyone know?
Click to expand...

Here are some date-related functions that Access has. You can look them up
in VBA help for more details:

Date(), Time(), Year(), Month(), Day(), Hour(), Minute(), Second(),
DatePart(), DateSerial(), DateAdd(), DateDiff(), Format(), Now()

These are all VBA functions, but Access's strange SQL dialect lets you call
these directly from a query.
--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
 
D

Dirk Goldgar

Maury Markowitz said:
I would like to make a function called Today that returns the start
of the day. I can't, because SQL Server doesn't appear to allow
GETDATE in a function. I can't imagine why this would be. Anyone know?
Click to expand...

You might do better asking in a SQL Server newsgroup, but I'm pretty
sure it's because GETDATE is nondeterministic; that is, it can return a
different value each time it is called, even with the same parameters.
Accordiong to BOL, such functions aren't permitted in SQL Server
user-defined functions (UDFs). I'm not sure why, because UDFs do not
have to be deterministic themselves. Can you use a stored procedure
instead?
 
M

Maury Markowitz

Dirk Goldgar said:
You might do better asking in a SQL Server newsgroup
Click to expand...

Yes, I selected the wrong one from the popup.
sure it's because GETDATE is nondeterministic; that is, it can return a
different value each time it is called, even with the same parameters.
Accordiong to BOL, such functions aren't permitted in SQL Server
user-defined functions (UDFs). I'm not sure why, because UDFs do not
have to be deterministic themselves. Can you use a stored procedure
instead?
Click to expand...

I guess. But you're on the same wavelength as me: the nondeterministic
functions, according to the docs themselves, have effects that are extremely
limited -- ie, you can't use them in indexed columns. THAT makes some sort of
sense, but a function?!

Maury
 
Top