Calling user-created functions with default values

The documentation talks about default values, but gives no examples. I'm
trying to get this to work...
ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
They say you need to pass in "default", but I can't figure it out. I tried...
select dbo.FirstDayOfYear(default)
select default dbo.FirstDayOfYear()
select dbo.FirstDayOfYear() default
Any pointers?
MauryI do not know why SQL Server let us create the function with the keyword
GETDATE as a parameter's default value. GETDATE is a function, so it should
be:
...
@.date datetime = getdate()
...
but then sql server give an error and this make sense. Try using a number
and you will see that it works (using default keyword when you call the
function).
alter FUNCTION FirstDayOfYear(
@.date datetime = 32500
)
RETURNS datetime
as
BEGIN
RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
END
go
select dbo.FirstDayOfYear(default)
go
I think you have to get rid of the default and allways pass a value to this
function.
AMB
"Maury Markowitz" wrote:
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||That's actually looking for the string, 'getdate' -- actually calling the
GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
accept that (I'm not sure why). You'll have to actually call the function
with GETDATE() as the argument to do what you need.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:24DEC598-40A2-491E-A975-D5E9026BD10D@.microsoft.com...
> The documentation talks about default values, but gives no examples. I'm
> trying to get this to work...
> ALTER FUNCTION FirstDayOfYear(@.date datetime = getdate) RETURNS datetime
> BEGIN
> RETURN convert(datetime, '1/1/' + convert(varchar, YEAR(@.date)))
> END
> They say you need to pass in "default", but I can't figure it out. I
tried...
> select dbo.FirstDayOfYear(default)
> select default dbo.FirstDayOfYear()
> select dbo.FirstDayOfYear() default
> Any pointers?
> Maury|||"Adam Machanic" wrote:
> That's actually looking for the string, 'getdate' -- actually calling the
> GETDATE function requires parens (GETDATE()) -- however, SQL Server will not
> accept that (I'm not sure why). You'll have to actually call the function
> with GETDATE() as the argument to do what you need.
Got it. It doesn't really need to have this feature -- a default that is --
but it would make the callee syntax a little nicer.
Maury