Answered Aug 28, 2020 · 0 votes
Hopefully, this example can help you to deal with your problem
DateTime dt = DateTime.Now; string str = dt.ToString("yyyy-MM-dd HH:mm:ss.000");- using (SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=**;Persist Security Info=True;User ID=**;Password=**;MultipleActiveResultSets=True;Connection Timeout=600; Application Name=EntityFramework")) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); try { SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO aa(IndexID,ChangeTime) VALUES (1, Convert(datetime, '{0}', 121)))",str )); cmd.Connection = conn; cmd.ExecuteNonQuery(); } catch { throw; } })
PS: more format information for you
Server string to date / datetime conversion - datetime string format sql server-- MSSQL string to datetime conversion - convert char to date - convert varchar to date-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000 -- Without century (yy) string date conversion - convert string to datetime functionSELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000 -- Convert string to datetime sql - convert string to date sql - sql dates format-- T-SQL convert string to datetime - SQL Server convert string to dateSELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyySELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with centurySELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyySELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyySELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy-- mon types are nondeterministic conversions, dependent on language settingSELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyySELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy-- 2016-10-23 00:00:00.000SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss-- 1900-01-01 20:10:44.000 -- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime formatSELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)-- 2016-10-23 11:02:44.013SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyySELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd-- YYYYMMDD ISO date format works at any language setting - international standardSELECT convert(datetime, '20161023')SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd-- 2016-10-23 00:00:00.000SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm-- 2016-10-23 11:02:07.577SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)-- 1900-01-01 20:10:25.300SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)-- 2016-10-23 20:44:11.000SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm-- 2016-10-23 20:44:11.500 -- Style 126 is ISO 8601 format: international standard - works with any language settingSELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)-- 2008-10-23 18:52:47.513SELECT convert(datetime, N'23 شوال 1429 6:52:47:513PM', 130) -- Islamic/Hijri dateSELECT convert(datetime, '23/10/1429 6:52:47:513PM', 131) -- Islamic/Hijri date -- Convert DDMMYYYY format to datetime - sql server to date / datetimeSELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)-- 2016-01-31 00:00:00.000-- SQL Server T-SQL string to datetime conversion without century - some exceptions-- nondeterministic means language setting dependent such as Mar/Mär/mars/márcSELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- DefaultSELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSISELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FRSELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy GermanSELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy ItalianSELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ssSELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msecSELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd JapanSELECT convert(datetime, '161023', 12) -- yymmdd ISOSELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dfltSELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC------------ -- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss-- String to datetime - mssql datetime - sql convert date - sql concatenate stringDECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6) SELECT @DateValue = '20120718', @TimeValue = '211920'SELECT @DateTimeValue =convert(varchar, convert(datetime, @DateValue), 111)+ ' ' + substring(@TimeValue, 1, 2)+ ':' + substring(@TimeValue, 3, 2)+ ':' + substring(@TimeValue, 5, 2)SELECTDateInput = @DateValue, TimeInput = @TimeValue,DateTimeOutput = @DateTimeValue;/*DateInput TimeInput DateTimeOutput20120718 211920 2012/07/18 21:19:20 */
C# to convert string to DateTime
DateTimeFormatInfo us = new CultureInfo("en-US", false).DateTimeFormat; DateTimeFormatInfo tw = new CultureInfo("zh-TW", false).DateTimeFormat; string result = Convert.ToDateTime("12/01/2011 下午 11:00:00 ", tw ).ToString("yyyy/MM/dd HH:mm:ss"); DateTime.ParseExact(result, "MM/dd/yyyy HH:mm:ss", CultureInfo.InvariantCulture);
Answered Jan 21, 2016 · 1 votes
The first part of the solution is to parse the Chinese date string. You can do this with the DateTime.Parse() method:
try{ string dateTimeString = "2016/1/20 下午 04:21:07"; DateTime createTime = DateTime.Parse(dateTimeString);}catch (FormatException e){ // The date string wasn't in a format that is recognized}
If the language of the computer you're running this on is set to something other than Chinese, then you'll need to explicitly specify that you're trying to parse a Chinese date:
var chineseCultureInfo = new CultureInfo("zh-CN");string dateTimeString = "2016/1/20 下午 04:21:07";DateTime createTime = DateTime.Parse(dateTimeString, chineseCultureInfo);-
Now that you have a proper DateTime object, you can insert it into SQL Server natively. We do this with a parameterized query so that we don't need to worry about getting the database server to try to parse our date strings.
SqlCommand cmd = conn.CreateCommand();-cmd.CommandText = "Insert into table1(indexid,createTime) values(1,@createTime))";cmd.CommandType = CommandType.Text;cmd.Parameters.AddWithValue("@createTime", createTime);cmd.ExecuteNonQuery();
If at some time in the future you need to support dates in another language's format, this solution has the added benefit of making it easy to parse those as well by just supplying a different CultureInfo to the Date.Parse() method above.
Answered Jan 21, 2016 · 0 votes
You could convert your datetime value in to a standard format before saving to the database. Whenever you retrieve the datetime back from the table you just need to convert it to the appropriate format for displaying using the Format() function. See this link for more details