Date Parameter in SQL Stored Procedure call

I have a rather complicated data collection process I need to run that takes a single date so I need to call an Microsoft SQL Stored Procedure to do the work.

I have the DataSetting.DatabaseType set correctly to dtMSSQLServer in the ppDesigner and the parm type is dtDate.

If I try to call it using 'EXEC dbo.myprocedure :MyDate' I get a 'Incorrect syntax near the keyword CONVERT' error from the designer.

If I use a simple 'Select * From sometable Where MyDate = :MyDate' then it works fine.

If I change the parameter type to a dtString it works for both the Select and the EXEC.

Any thoughts? I would like to keep the parm a date type to avoid invalid dates when prompted.
Everyone has a photographic memory, but not everyone has film.


  • Hi Tom,

    RB is using an expression to normalize the date. From Google, MS SQL does not accept expression for a Stored Proc param value.

    Have a look at TdaSQL.FormatParamValue (daSQL.pas). Try commenting out the 'if (IsMSSQLServer) then..' block of code towards the bottom. Test and see whether that works.

    Best regards,

    Nard Moseley
    Digital Metaphors
Sign In or Register to comment.