How to enforce specific date format in T-SQL?

I have a table that contains records, with a DATETIME column. I am writing a stored proc that will take a startTime and endTime and return all records between these two times. I want to force users to provide the time in a specific format ('YYYY-MM-DDThh:mm:ss.nnn'). The reason bein开发者_StackOverflow中文版g I don't my table contains a lot of records, and I don't want to limit the data retrieval to two points in time during the same day only (so they must provide times to a seconds granularity at least).

Is there anyway to enforce this?

Thanks.

---

Yes, but then you have to take the parameters as strings (varchar) instead of datetime, as the datetime values 2010-12-08 and 2010-12-08 00:00:00.000 are the same.

You can verify that the length of the input strings meet the requirement, and then convert them to datetime using convert(datetime, startTime, 126).

---

You can force specific format only if you're accepting string as date - then, you could try to parse that string with your format and if it fails you throw error.

But, if your stored proc accepts DATETIME variable there's no 'format' - it's simply a variable with value.

---

You mean something like:

IF   @arg NOT LIKE    '19[0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]'
 AND @arg NOT LIKE '2[01][0-9][0-9]-[0-1][0-9]-[0-3][0-9]T[0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]'
RAISERROR('Wrong format', 10, 1)

?

Only if @arg is passed as a string of course.

---

As long as the users provide any valid date format, you should accept it. Learn to use CAST and CONVERT instead:

http://msdn.microsoft.com/en-us/library/ms187928.aspx