DateTimeFormat issue when pass the parameter to MS-Sql stored procedure

Question

I have a stored procedure, which has a parameter like @CurrentDate datetime, when I pass the value DateTime.Now from the front end (C#) to this stored procedure it is working fine.

But when I change the Date Format as English(India) in my system/server, that time DateTime.Now will return a value like 25-10-2012 PM 05:23:27. I am passing this value to stored procedure and I'm getting an error message like the following,

Msg 242, Level 16, State 3, Line 10
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Note : I don't want to use GETDATE() from Sqlserver, I want to pass the parameter from c#.

How can I solve this?

My Sql server is running different machine, which is different location... I need a server date only which is running IIS Server. — Manikandan Sethuraju
– Manikandan Sethuraju, Commented Oct 25, 2012 at 13:14
Then the correct way of doing this is to store date using GETUTCDATE() on sql server side. Then in your code, before you display the time to the user (if required), you can convert it to the user's local timezone and display it. You can use TimeZoneInfo class to get locale specific datetime, for your datetime comparisons. — tranceporter
– tranceporter, Commented Oct 25, 2012 at 13:16

Jegadeesh Waran · Accepted Answer · 2015-07-20 06:46:32Z

1

The problem with the date format is because you are converting it to a string and then back to a DateTime value. Depending on the culture settings on the specific server this may or may not work. It may also misinterpret the data, e.g. transforming a date from 2013-10-12 to 2013-12-10.

DateTime datet = new DateTime(year,month,day);
  startDateParam.Value = datet;
  endDateParam.Value = datet;

"note that the server stored datetime with format '1900-01-01 00:00:00.000'"

answered Jul 20, 2015 at 6:46

Jegadeesh Waran

656 bronze badges

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

Comments

harriyott · Accepted Answer · 2012-10-25 13:32:53Z

0

You can use this conversion from C#. Hope this helps

DateTime.Now.ToString(DateTimeFormatInfo.InvariantInfo)

edited Oct 25, 2012 at 13:32

harriyott

10.7k10 gold badges69 silver badges104 bronze badges

answered Oct 25, 2012 at 13:29

Thangamani Palanisamy

5,3204 gold badges36 silver badges39 bronze badges

1 Comment

Manikandan Sethuraju Over a year ago

yes @Thangamani Palanisamy.... Thank you very much... It's working fine without any convertions & also dont need to change parameter datatype....

Kinexus · Accepted Answer · 2012-10-25 13:13:36Z

0

You will need to convert the value to a valid DateTime using Convert function.

For more information : http://www.sql-server-helper.com/tips/date-formats.aspx

answered Oct 25, 2012 at 13:13

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

1 Comment

Manikandan Sethuraju Over a year ago

Parameter value is 25-10-2012 PM 05:23:27, so i want to change the parameter datatype from datetime to nvarchar and then want to convertion.. right?

tranceporter · Accepted Answer · 2012-10-25 13:30:24Z

0

Storing dates in local timezones can cause headache when you launch internationally. If you save current date using GETUTCDATE() on SQL Server side, you can get the time in user specific timezone as follows:

public DateTime GetDateByTimeZoneId(DateTime dateTime, string timeZone)
{
   if (dateTime == null)
   {
        return null;
   }

   dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
   return TimeZoneInfo.ConvertTimeFromUtc(dateTime, TimeZoneInfo.FindSystemTimeZoneById(timeZone));
}

You can then pass user relevant timezone like "GMT Standard Time"

edited Oct 25, 2012 at 13:30

answered Oct 25, 2012 at 13:23

tranceporter

2,2811 gold badge21 silver badges23 bronze badges

Collectives™ on Stack Overflow

DateTimeFormat issue when pass the parameter to MS-Sql stored procedure

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related