Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

problem with dates in parameter

Report query uses @.startDate and @.endDate for current date and tomorrow.
For currentdate I use =Today and that seems ok but if the report is run at
3PM the time shows 3PM rather than 00:00:00.
EndDate is more problematic. I've tried many different suggestions from
within this forum for obtaining tomorrow's date, including
=DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
right.
Can someone please help correct the syntax? Thanks!Do you need to use the full date and time, or is it sufficient to use date?
If you only need the date, you could get your dates by doing this sql query:
select convert(varchar(10), getdate(), 104) as today, convert(varchar(10),
dateadd("d", 1, getdate()), 104) as tomorrow
Just switch 104 to get the right date format.
Kaisa M. Lindahl Lervik
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:5D023CCF-CCB6-4A2B-960E-74091184774F@.microsoft.com...
> Report query uses @.startDate and @.endDate for current date and tomorrow.
> For currentdate I use =Today and that seems ok but if the report is run at
> 3PM the time shows 3PM rather than 00:00:00.
> EndDate is more problematic. I've tried many different suggestions from
> within this forum for obtaining tomorrow's date, including
> =DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
> right.
> Can someone please help correct the syntax? Thanks!