setting a default date to an SP's parameter 1

[1DMF]

I seem unable to work out the syntax to set a stored procedure paramter to a default date.

I have tried

@EndDate char(10) = GETDATE

But that errors with "Conversion failed when converting date and/or time from character string."

I then tried

@EndDate char(10) = CONVERT(char(10),GETDATE(),126)

But that won't compile.

I want to set a default if no date is passed to the SP as the current date in 'YYYY-MM-DD' format.

How would I do this?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[Rhys666]

Personally, I would make the parameter nullable then at the top of the procedure do a NULL check on it and set it to CURRENT_TIMESTAMP if it is NULL.

    ...some parameters...
    @EndDate char(10) = NULL
    ...some more parameters...

...procedure actual activity starts...
SELECT @EndDate = ISNULL(@EndDate, CURRENT_TIMESTAMP);

...procedure actual activity carries on...

If that makes any sense

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[gmmastros]

Set the parameter to null. Within the stored procedure, as the first line, use:

Set @EndDate = coalesce(@EndDate, Convert(char(10), GetDate(), 121))


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Thanks George,

I was close I found this on stack overflow

Set @currentDate = Coalesce(@currentDate , GetDate())

and was about to have a play!

Your help as always is much appreciated.

1DMF.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads