Converting getdate to Varchar

[MayoorPatel]

Hi there I'm trying to assign a default value to the parameter @EndDate but I keep getting this error when I execute the procedure and pass no parameter to the @endate

Msg 241, Level 16, State 1, Procedure p_GetAllCardsIssuedByOrganisation, Line 0
Conversion failed when converting datetime from character string.

@OrganisationID int,
@StartDate datetime = '1-jan-2005',
@EndDate datetime = getDate
as
select max(v.verifierID),count(firstname), dbo.getmonth(SignUpDate),max(dbo.getmonthdate(SignUpDate)) from tblusers u
inner join dbo.GetVerifiersByOrganisation(@OrganisationID) v
on u.createdby = v.verifierID
where usertypeID=1
and dbo.getmonth(SignUpDate) between dbo.getmonth(@startdate) and dbo.getmonth(@enddate)
group by v.organisationID,dbo.getmonth(SignUpDate)
order by dbo.getmonth(SignUpDate)

 

[gmmastros]

Change GetDate to GetDate[!]()[/!]

The parenthesis are important.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[MayoorPatel]

ok im now getting

Msg 102, Level 15, State 1, Procedure p_GetAllCardsIssuedByOrganisation, Line 7
Incorrect syntax near '('.

 

[gmmastros]

According to Books On Line...

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. [!]The default must be a constant or it can be NULL[/!]. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

So... You should set the default value to NULL. Inside the procedure, you can test for null and set the @EndDate value to GetDate(). Like this:

@OrganisationID int,
@StartDate datetime = '1-jan-2005',
@EndDate datetime = [!]NULL[/!]
as

[!]Set @EndDate = IsNull(@EndDate, GetDate())[/!]

select max(v.verifierID),count(firstname), dbo.getmonth(SignUpDate),max(dbo.getmonthdate(SignUpDate)) from tblusers u
inner join dbo.GetVerifiersByOrganisation(@OrganisationID) v
on u.createdby = v.verifierID
where usertypeID=1
and dbo.getmonth(SignUpDate) between dbo.getmonth(@startdate) and dbo.getmonth(@enddate)
group by v.organisationID,dbo.getmonth(SignUpDate)
order by dbo.getmonth(SignUpDate)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom