Take the 2-minute tour ×

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.

share|improve this question
What value are you passing to the @TimeItemAdded parameter in InsertItemDetails? –  LittleBobbyTables Jul 25 '12 at 14:08
20/07/2012 00:00:00 as date and time –  fdgfdgs dfg Jul 25 '12 at 14:08
add comment

3 Answers

up vote 2 down vote accepted

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

You should pass the date in as:

share|improve this answer
cheers, problem solved, i will accept it in 9 minutes –  fdgfdgs dfg Jul 25 '12 at 14:10
This is not a safe format. SET LANGUAGE FRENCH; SELECT CONVERT(DATETIME, '2012-07-20 00:00:00'); yields Msg 242, Level 16, State 3, Line 2; La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites. Safe formats for datetime are YYYYMMDD or YYYY-MM-DDTHH:MM:SS.nnn - that T is important. –  Aaron Bertrand Jul 25 '12 at 14:12
@AaronBertrand - fascinating; removed the time for consistency –  LittleBobbyTables Jul 25 '12 at 14:12
Still not language safe even without the time. The dashes are the problem. In French (and some other locales) this is interpreted as YYYY-DD-MM. Obviously there is no 20th month. YYYYMMDD is immune to this problem. –  Aaron Bertrand Jul 25 '12 at 14:13
Interesting; we have some French sites, and I swear this has never come up, but I believe you. –  LittleBobbyTables Jul 25 '12 at 14:15
add comment

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.

share|improve this answer
add comment

This link has all the datetime formats, their conntries http://www.sql-server-helper.com/tips/date-formats.aspx

You could pass it in as a varchar and explicitly convert it to a datetime using the proper date format code. For example:

SELECT CONVERT(DATETIME, '20/07/2012 00:00:00', 103)

103 is the datetime code you are looking for.

share|improve this answer
I don't agree that you should change the parameter to varchar. Fix the format (or use the right data type) as early as possible. If you change the parameter to varchar, people can pass in anything they want. mm/dd/yyyy will break, for example; 06/07/2012 will just store the wrong date silently, and 07/20/2012 will generate an exception. –  Aaron Bertrand Jul 25 '12 at 14:29
add comment

Your Answer


