Thread: User Defined Function
11-10-04, 14:24 #1Registered User
- Join Date
- Nov 2003
- South Africa
Unanswered: User Defined Function
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
11-10-04, 17:09 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
You can get arround the limitations of getdate() within UDF by creating a view:
create view dbo.vw_getdate (CurrentDateTime) as select getdate()
Then you can use this view within your function, OR create another function that returns datetime by referencing the view:
create function dbo.fn_getdate()
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."