Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    South Africa

    Question 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

  2. #2
    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()
    returns datetimeasbegin
    select*from dbo.vw_getdate
    select 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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts