Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
antonio@compuconsult.net
Starting Member
8 Posts |
 Posted - 2003-08-04 : 00:12:41
|
| Hello All,Ok I thought this would be simple. CREATE FUNCTION [dbo].[Accounting_CalculateDaysOverDuel] (@tempDate datetime)RETURNS intAS BEGINDECLARE @DOD int IF DATEDIFF(day, @tempDate, getdate()) - 30 < 0 BEGIN SET @DOD = 0 END SET @DOD = (DATEDIFF(day, @tempDate, GETDATE())) - 30 RETURN @DODENDAll I want to due is get the days overdue if I pass this function a date. But I keep getting "Invalid use of getdate within a function"What am I doing wrong?Tony |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-04 : 00:24:43
|
HiIn BOL under "User Defined Functions"quote:
Built-in functions that can return different data on each call are not allowed in user-defined functions
Maybe you could pass the current date into the function as a parameter.Damian |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-04 : 02:56:36
|
| Basically getdate() is not deterministic.It is possible to call getdate() from a function by tricking the server but this will cause the optimiser to give an incorret plan (it expects the function to be deterministic) and queries to give incorrect results.As Merkin says you have to pass the value to the function.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
GetDate