Optional Parameters with User Defined Functions
Does anyone know if it's possible to have an optional input parameter with
Functions (just as you can with Stored Procedures) .
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
@CurDateTime datetime = getdate,
@GMTDateTime datetime = getutcdate
Using Standard Function from within User Defined Function in SQL
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...
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.
Originally Posted by arunav
Here are some other workarounds:
I've seen a few others used too but can't remember them off the top of my head...
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL