0

I want to convert below date and time format to SQL date and time Thu Apr 07 2016 06:30:00 GMT+0530 (India Standard Time) any one have idea Thanks.

Chenna Rao
  • 111
  • 3
  • 11
  • What is the input string? Does it really contain both `GMT+0530 (India Standard Time)` ? Did you try to parse it to a DateTimeOffset before sending it to SQL Server? What is the type of the column? DateTimeOffset (good) or just DateTime (forget timezones?) – Panagiotis Kanavos Apr 07 '16 at 14:01

2 Answers2

1

Since your string has UTC Offset value, I would parse it to DateTimeOffset instead of DateTime since it can hold the offset part.

But neither DateTime nor DateTimeOffset keeps time zone information, you should use GMT and (India Standard Time) parts as a string literal delimiter.

var s = "Thu Apr 07 2016 06:30:00 GMT+0530 (India Standard Time)";
var dto = DateTimeOffset.ParseExact(s, "ddd MMM dd yyyy HH:mm:ss 'GMT'zzz '(India Standard Time)'", 
                                    CultureInfo.InvariantCulture);

Now you have a DateTimeOffset as {07.04.2016 06:30:00 +05:30}.

enter image description here

And I would insert this dto as datetimeoffset typed column in SQL Server (with a parameterized query of course) since it saves offset part as well.

+---------------------------+
|   Time zone offset range  |
+---------------------------+
|   -14:00 through +14:00   |
+---------------------------+
Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
0

If your input is a string, you will need to start parsing the date for the specific culture:

DateTime dt =  DateTime.ParseExact(inputString, System.Globalization.CultureInfo("<your_culture>"));

where <your_culture> is one of the multiple culture names for your country (see http://www.csharp-examples.net/culture-names/)

then you can get the date back as an SQL-compatible string, with simple quotation marks included:

string sqlDate = dt.ToString("'yyyy-MM-dd HH:mm:ss'");
Adam Calvet Bohl
  • 1,009
  • 14
  • 29
  • DateTime doesn't support timezones or offsets. The proper type to use is DateTimeOffset – Panagiotis Kanavos Apr 07 '16 at 14:01
  • Off course, this will only convert between formats. From the question, I did not infer that he needs also time-zone conversion. – Adam Calvet Bohl Apr 07 '16 at 14:26
  • Storing or passing dates as text in the database is *not* a good idea. It's far better and easier to use date parameters and avoid conversions and formatting errors. For example, the format in this answer is *not* "SQL compatible". It may look like the ISO 8601 format but is missing the `T` separator. It will be parsed using the locale of the server – Panagiotis Kanavos Apr 07 '16 at 14:31