Cannot pass date into stored procedure

Question

Trying to pass date into a stored procedure as follows:

@dateRegistered = '28/04/2012'

But it keeps telling me:

Error converting data type varchar to date.

It works if I do it as:

@dateRegistered = '04/28/2012'

But this is not the format I want to use.

I have run the following query to set the format:

SET DATEFORMAT dmy

Why isn't it working?

Edit: Here is how I did it:

In my stored procedure, I put:

SET DATEFORMAT dmy

Then from code, I pass it as:

myCommand.Parameters.Add("@dateRegistered", SqlDbType.Date);
myCommand.Parameters["@dateRegistered"].Value = DateTime.Now.ToString("dd/mm/yyyy");

I passed it as string to ensure that even if my computer (or the server) has a different date format, it will always use dmy.

Edit Edit:

I passed it as datetime.now. It seems to transform it to the default format when I send it, and then transform it back to my computer format when I read it. Unsure how exactly this happens, but it seems to be working fine.

"I passed it as string to ensure that even if my computer (or the server) has a different date format, it will always use dmy.". NO, NO, NO Pass it as a DateTime - the .NET libraries know how to translate the .NET concept of datetime into the SQL concept, and don't need to muck about with any string formatting or ambiguity. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 28, 2012 at 11:26

Fredrik Mörk · Accepted Answer · 2012-04-28 10:56:09Z

3

It looks like the parameter is a date (in the database), while you try to pass a string (which corresponds to varchar in the database). If you instead pass a .NET DateTime object it should work as expected.

answered Apr 28, 2012 at 10:56

Fredrik Mörk

159k31 gold badges296 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheGateKeeper Over a year ago

So how do you pass the date in the sql management studio? (For testing)

Fredrik Mörk Over a year ago

@TheGateKeeper in your case you could try convert(datetime, '04/28/2012', 101) (assuming you are using SQL Server). I am not a SQL Server master so there may be better ways.

Andomar Over a year ago

If this answer were correct, it would also fail for the month-first format, but it doesn't

marc_s · Accepted Answer · 2012-04-28 11:33:01Z

2

Save yourself the headache and use an unambiguous date format - 20120428 (YYYYMMDD) or 2012-04-28.

As an aside: you mention you're using C# (based on the tags) - how are you using ADO.NET? Or is it Linq to SQL / EF / some other ORM?

Edit:

Ok, use the CONVERT function with an appropriate parameter indicating the format of your date string

EG:

declare @D datetime
set @D = CONVERT(datetime, '04/28/2012', 101)
print @D

101 is US standard format mm/dd/yyyy.

Using convert will ensure it ALWAYS works regardless of any environment settings such as the date order.

edited Apr 28, 2012 at 11:33

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 28, 2012 at 10:57

Ian Yates

1,36413 silver badges25 bronze badges

3 Comments

TheGateKeeper Over a year ago

It seems to work from code when I pass a datatime object... but I want to be able to test it from the SQL Management Studio too. Btw, I don't want to use the (YYYYMMDD) format.

Damien_The_Unbeliever Over a year ago

2012-04-28 doesn't work in SQL Server, for a datetime, if you've got british language set. (20120428 does)

TheGateKeeper Over a year ago

Can you please check If I did it correctly? I updated the question.

Andomar · Accepted Answer · 2012-04-28 11:11:23Z

1

If you call set dateformat dmy, that only affects the current connection. You'd have to set it every time right before you convert the string to a date.

The best solution is probably the ISO format, like @IanYates suggests.

answered Apr 28, 2012 at 11:11

Andomar

239k55 gold badges387 silver badges412 bronze badges

4 Comments

TheGateKeeper Over a year ago

So it isn't something you set for the database one time? I have to set it each time I pass a parameter?

Andomar Over a year ago

Or better, pass a datetime myCommand.Parameters["@dateRegistered"].Value = DateTime.Now;

TheGateKeeper Over a year ago

Can you please explain how to use the ISO format? If I call DateTime.Now wont this return the date with the format specified to the computer?

Andomar Over a year ago

ISO format is DateTime.Now.ToString("yyyy-MM-dd"), note that mm means minutes, and MM months, see MSDN

Ankit Gupta · Accepted Answer · 2014-01-21 12:11:25Z

1

dear your code is working after using * SET DATEFORMAT dmy because you are passing date as "dd/MM/yyyy" like "DateTime.Now.ToString("dd/mm/yyyy")"

check the date format in your database by following command :-

dbcc useroptions

and check "dateformat : mdy" by default. So passing your date in "MM/dd/yyyy" format will also work fine.

Happy Coding :)

answered Jan 21, 2014 at 12:11

Ankit Gupta

112 bronze badges

Collectives™ on Stack Overflow

Cannot pass date into stored procedure

4 Answers 4

3 Comments

3 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related