Validating Date Parameter in SQL Server Stored Procedure

Question

I've written a stored procedure that takes in a date parameter. My concern is that there will be confusion between American and British date formats. What is the best way to ensure that there is no ambiguity between dates such as 02/12/2008. One possibility would be for users to enter a date in a format such as 20081202 (yyyymmdd). Is there any way to validate that without using sub strings? Alternatively dates could be entered as 02-Dec-2008(dd-mmm-yyyy), but again verification is not trivial and there are potential issues with users who do not use English.

Further to the first three answers . . . One issue is that I'm expecting this stored proc to be called directly without a front end so validation ouside of the proc is not an option. Is it a good idea to take the day, month and year as separate parameters?

Answer 1

You won't have any problems whatsoever if you'd use parameters in your sproc:

create proc dbo.Sproc
    @date datetime
as
    ...