Error converting data type nvarchar to datetime

Question

I have the following procedure interface:

Create procedure [dbo].[InsertItemDetails]
    @TimeItemAdded datetime

When I call it this way:

EXEC [dbo].[InsertItemDetails]
     @TimeItemAdded = N'20/07/2012 00:00:00';

I get this error:

Msg 8114, Level 16, State 5
Error converting data type nvarchar to datetime.

Answer 1

Depending on your regional settings, the parameter you are passing in for @TimeItemAdded might not be recognized.

You should pass the date in as:

20120720

Answer 2

Use an unambiguous string literal for your date. In this case:

EXEC dbo.InsertItemDetails
    ...
    , @TimeItemAdded = '20120720';

Better yet, make sure to pass a strongly typed parameter where you know that the date is correct. Ideally, this should never be presented as a string in any format.

Regional formats like m/d/y are bad news, because you can't ensure that they will work given the user's session, dateformat, language settings, the regional settings on the machine, etc.