How To Convert Data Format with Chinese words (下午/上午) In T-SQL Script with C# (based on SQL Server with Chinese Version)

Question

I have a problem with SqlCommand in C#, I need to insert DateTime format to SQL Server like as following code

For example

using (SqlConnection conn = new SqlConnection("Data Source=127.0.0.1;Initial Catalog=B2CCompareData;Persist Security Info=True;User ID=sa;Password=1111;MultipleActiveResultSets=True;Connection Timeout=600; Application Name=EntityFramework"))
{
    conn.Open();                    
    SqlCommand cmd = conn.CreateCommand();                   

    try
    {
        cmd.CommandText = "Insert into table1(indexid,createTime) values(1,Convert(datetime, '2016/1/20 下午 04:21:07', 121))";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;                   

        cmd.ExecuteNonQuery();                   
    }
    catch
    {
        throw;
    }
}

2016/1/20 **下午** 04:21:07 ==> The datetime with mixing Chinese words

But unfortunately I get an error message:

Conversion failed when converting date and/or time from character string.

So that what can I do to fix this problem?

PS: 上午 is AM / 下午 is PM

@KrishnaPS thanks for your comment , please tell me how to do — , Jan 21 '16 at 06:11
@WeeWei in my opinion you can use c# to transfer data to data format , — Willie Cheng, Jan 21 '16 at 06:21

Ben · Answer 1 · 2016-01-21T07:19:19.873

Step 1 - Convert your date string into a DateTime

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);

Step 2 - Insert DateTime into SQL Server with a parameterized query

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.

score 0 · Answer 2 · answered Jan 21 '16 at 06:13

0

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

answered Jan 21 '16 at 06:13

Martin

644
5
11

I have tried it many time , but it can not solve this problem , do you have any sample code ,thanks – Jan 21 '16 at 06:26

score 0 · Answer 3 · answered Jan 21 '16 at 06:19

0

could you just replace it? replace(replace(column1,'上午','AM'),'下午','PM').

answered Jan 21 '16 at 06:19

Raffaello.D.Huke

552
2
9

score 0 · Answer 4 · edited May 23 '17 at 11:44

0

This is according to Stack Overflow post

If you need to convert a string to a DateTime you could try

DateTime dt = DateTime.Parse("01:00 PM"); // No error checking

or (with error checking)

DateTime dt; bool res = DateTime.TryParse("01:00 PM", out dt);

Variable dt contains your datetime, so you can write it

dt.ToString("HH:mm");

Last one works for every DateTime var you have, so if you still have a DateTime, you can write it out in this way.

Get the time in 24 hour format and store it in DB.

edited May 23 '17 at 11:44

Community

1
1

answered Jan 21 '16 at 06:22

Krishna P S

340
2
9

this is Chinese language system , time from system so that I need to transfer from it and then send it to db – Jan 21 '16 at 06:29
2

@WeeWei That's why you need to convert it into 24 hour format. – Krishna P S Jan 21 '16 at 06:31
1

@WeeWei You can try Convert.ToDateTime("2016/1/20 下午 04:21:07").ToString("HH:mm"); – Krishna P S Jan 21 '16 at 06:32
1

@WeeWei Is your date coming from a Datepicker? – Krishna P S Jan 21 '16 at 06:32
agree with Krishna, you really need to convert the system time into 24 hour format, that will solve the problem at the very first place and won't happen again – Raffaello.D.Huke Jan 21 '16 at 07:14

Willie Cheng · Accepted Answer · 2020-08-28T06:49:03.640

Hopefully, this example can help you to deal with your problem

For example C#

 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 function
SELECT 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 date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT 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 format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT 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 setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
SELECT convert(datetime, N'23 شوال 1429  6:52:47:513PM', 130) -- Islamic/Hijri date
SELECT convert(datetime, '23/10/1429  6:52:47:513PM',    131) -- Islamic/Hijri date
  
-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT 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árc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT 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:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT convert(datetime, '161023', 12) -- yymmdd ISO
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT 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 string
DECLARE @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)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput   TimeInput   DateTimeOutput
20120718    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);

Jerrime25 · Answer 6 · 2016-01-21T07:06:32.450

0

  cmd.CommandText = "Insert into table1(indexid,createTime) values(1, "+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+")";
  cmd.CommandType = CommandType.Text;
  cmd.Connection = conn;                   
  cmd.ExecuteNonQuery();

edited Jan 21 '16 at 07:06

answered Jan 21 '16 at 06:45

Jerrime25

13
1
8

score 0 · Answer 7 · answered Jan 21 '16 at 06:55

Not a very intuitive way but you can replace the '下午' and '上午' characters with nothing and then add am or pm at the end of your string. Now you can convert it to datetime

DECLARE @string NVARCHAR(30) = N'2016/1/20 下午 04:21:07'

SELECT CONVERT(DATETIME, REPLACE(REPLACE(@string, N'下午', ''), N'上午', '') +
                         CASE WHEN ISNULL(CHARINDEX(N'下', @string), 0) > 0 THEN ' pm' 
                              WHEN ISNULL(CHARINDEX(N'上', @string), 0) > 0 THEN ' am'
                         END , 121)

Output

2016-01-20 16:21:07.000

How To Convert Data Format with Chinese words (下午/上午) In T-SQL Script with C# (based on SQL Server with Chinese Version)

7 Answers7

Step 1 - Convert your date string into a DateTime

Step 2 - Insert DateTime into SQL Server with a parameterized query

Linked