August 28, 2003 at 5:24 pm
Since SQL Server does not permit the user of GETDATE() inside a UDF because it is 'indeterminate', is there any way to use date data in my own functions. I want to automatically assign a ID number based on the @CustomerCode + @Year + SeqNbr and have hit this roadblock. If I feed the '03' part to the function it works GREAT. That implies I can't use UDFs to automatically assign the numbers during UPDATEs.
August 28, 2003 at 6:00 pm
Create View MyDate as Select GetDate() as TheSystemDate
GO
Create Function SomeFunction (@CustomerCode Int)
Returns VarChar(20) AS
Begin
Declare @Year Int
Select @Year=DatePart(Year,TheSystemDate) From dbo.MyDate
Return(Cast(@CustomerCode as VarChar(10))+Cast(@Year as Varchar(4))+'01')
End
GO
Select 'Answer',dbo.SomeFunction(10)
GO
Drop function SomeFunction
GO
Drop View MyDate
GO
August 29, 2003 at 2:22 pm
That function is still not deterministic, though.
I've been playing around with this, but I can't get a "Get current date" function to be deterministic.
if object_ID('SystemDate') is not null drop view SystemDate
go
Create View SystemDate with schemabinding as Select GetDate() as SysDate
GO
If object_id('GetSystemDate') is not null
drop function GetSystemDate
go
Create Function GetSystemDate ()
returns datetime
with schemabinding as
begin
declare @Date datetime
select top 1 @Date = SysDate from dbo.Systemdate
return @Date
end
go
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.GetSystemDate'), 'IsDeterministic')
select dbo.GetSystemDate ()
Signature is NULL
August 29, 2003 at 11:38 pm
Your objective is maintain a key value, which is a combination of the Customercode, Year and running serial number. The simple way to do it is,
Cast(@CustomerCode as varchar(n))+ cast(year(getdate()) as varchar(4)) + @srl_no
Don't complicate things, you will only increase your processing time. You can retrive Day/Month/year of GETDATE().
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply