1

I am working on C# SQL.

I've a datetime value "1/10/2016 12:00:00 AM" for eg 

Datetime dt="1/10/2016 12:00:00 AM"

When I passed this value to SQL stored procedure as parameter, it changed to 10 Jan 2016

I need the result as 1 OCT 2016 in SQL. Sql stored procedure parameter data type is datetime.

Many Thanks

Mohit S
  • 13,723
  • 6
  • 34
  • 69
JMK
  • 27
  • 1
  • 3
  • can u show the code. How you are storing it in `dt` and how the SQLParameter looks like – Mohit S Jan 23 '17 at 09:17
  • 4
    Sounds you're passing it as string, you should pass it as datetime – HoneyBadger Jan 23 '17 at 09:17
  • Please take a look at this [question](http://stackoverflow.com/questions/2191120/net-datetime-to-sqldatetime-conversion). – Manuel Koch Jan 23 '17 at 09:18
  • 1
    If passing as a string, format it `yyyy-MM-dd HH:mm:ss` to ensure it's correctly interpreted. Sending as `dd/MM/...` can easiily be interpreted as 'MM/dd/...' if your database language is `en-US` instead of `en-GB`. The other option's to set your database language; but I prefer the first method since this works regardless. – JohnLBevan Jan 23 '17 at 09:19

3 Answers3

1

Change the datetime value and see what happened. If still coming the 10 Jan 2016, then it may be changes that stored procedure is taking the default value which is store value is 10 Jan 2016.

s.k.Soni
  • 1,139
  • 1
  • 19
  • 37
  • I know Zohar Peled. But as per the stackoverflow rules we can't do comment if we are below 50 reputation. So for this limit I want to as a answer not a comment. – s.k.Soni Jan 23 '17 at 09:44
0

Use parameters to pass the values to sql query. Or use myDateTime.ToString("s"); this for datetime value

"s" - Standard datetime format

kgzdev
  • 2,770
  • 2
  • 18
  • 35
0

Well, are you sure that the value in c# is in fact 1 OCT 2016 and not 10 Jan 2016?
The row: 

Datetime dt = "1/10/2016 12:00:00 AM";

Doesn't even compile.

Best practice for passing DateTime values between c# and Sql Server is using sql parameters of type DateTime or DateTime2 and passing an instance of the DateTime struct.
If you are doing that, then the DateTime values are guaranteed to pass correctly.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121