SQL: datetime or varchar as parameter datatype for stored procedure

Question

I have a SQL server stored procedure which would accept date as input param to build a query in it.So which datatype i should use for the parameter when defining stored procedure.Whats the difference between using nvarchar(15) and datetime .

1 : create procedure TestProcedure(@startDate datetime)
        and
2 :   create procedure TestProcedure(@startDate nvarchar(15))

IS there any advantage is i use datetime over varchar in terms of performance

Answer 1

If you know that only dates will come through the parameter, using the appropriate datatype is better as it's smaller and comparisons can be made quicker. It also prevents invalid values from being supplied.

Answer 2

Yes, always use the datetime type. Its more accurate. The datetime type is also always 8 bytes so it is smaller to transfer than 15 characters.

The front end is then responsible for handling cultural/locale issues such as MDY, DMY or YMD component field ordering. (if the database is running under a US locale and the user is in the UK does '3/4/2009' mean April 3 or March 4?)