Default date parameter won''t work.

• eheraux

  Old Hand

  Points: 393

  More actions

  February 20, 2005 at 7:00 pm

  #66503

  Executing something just like the Proc below gave me "Error 241: Syntax error converting datetime from character string." As you can see, I'm trying to default that one parameter to today's date. I can't find a rule governing why this won't work when passed DEFAULT as the parameter. Passing a regular date works fine. I get the same error even if I set the parameter up as a varchar. How do I get this to work when passed DEFAULT from my frontend app?

  I tried to CAST or CONVERT the parameter in various parts of the proc, but that still won't do it.

  ***************************************************

  CREATE PROC NWtest @endDate datetime=getdate

  AS

  SELECT OrderDate

  FROM Northwind.dbo.Orders

  WHERE OrderDate<@enddate

  Order By OrderDate

  GO

  EXEC NWtest DEFAULT

  GO

  ***************************************************

  Many thanks,

  -Ed H.

• philcart

  SSC-Forever

  Points: 47794

  More actions

  February 20, 2005 at 8:12 pm

  #543248

  As per Books Online, the default must be a constant or NULL.

  You could re-code your stored procedure like this,

  CREATE PROC NWtest 
   @endDate datetime = NULL
  AS

  IF @enddate IS NULL
   SET @endDate = GetDate()

  SELECT OrderDate
  FROM Northwind.dbo.Orders
  WHERE OrderDate<@enddate
  Order By OrderDate

  GO

   

   

  --------------------
  Colt 45 - the original point and click interface