Optional Parameters with User Defined Functions

[Eoin Beck]

Hello !

Does anyone know if it's possible to have an optional input parameter with
Functions (just as you can with Stored Procedures) .

Backround:

The reason I'm asking is that I am writing a function to convert a time that
is passed in as a paramater into a propriatory format (FIX format for anyone
that is in brokerage) In addition to the formating of the date/time, the
time needs to be converted to GMT time. I'm doing that by comparing the
hour difference between the current time (using getdate()) and the current
GMT time (using getutdate()) . I add this result to the date passed in to
get that time in GMT time then convert the new datetime to the custom format.


I tried calling getdate() and getutcdate() from within the Function, but
I soon found out that those functions can not be called from within a Function.

I then tried using those functions as optional paramaters. Here's the syntax:

CREATE FUNCTION BOSSConvertDateToTranTime
(
@DateToConv datetime,
@CurDateTime datetime = getdate,
@GMTDateTime datetime = getutcdate
)

[arunav]

Well you can ofcourse call getdate and getutcdate from within the user defined function the point is that user defined functions are scalar objects you need to call them like...

dbo.getutcdate()

dbo.getdate()


Regards

Arunav
Microsoft .Net Guru (www.arunav.net)

[Wiseman82]

Well you can ofcourse call getdate and getutcdate from within the user defined function the point is that user defined functions are scalar objects you need to call them like...

dbo.getutcdate()

dbo.getdate()


Regards

Arunav
Microsoft .Net Guru (www.arunav.net)

You can't call non-deterministic system functions from a user defined function. There are some workarounds to get the current date - one of which is to pass it as a param.

[Wiseman82]

Here are some other workarounds:

http://www.aspfaq.com/show.asp?id=2439

I've seen a few others used too but can't remember them off the top of my head...