Converting DateTime of SQLServer to MySQL

Question

I am developing an app in VS2010 c# to fetch a single row data from SQLServer and insert it to MySQL.

I have fetched data and stored in particular string variable. Fetching code is as below.

SqlCommand cmd = new SqlCommand("SELECT TOP (1) s_name, s_city, s_address, s_added_date, s_added_by FROM tblAQI ORDER BY s_added_date DESC", SSCon);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            s_name = (dr["s_name"].ToString());
            s_city = (dr["s_city"].ToString());
            s_address = (dr["s_address"].ToString());
            s_added_date = (dr["s_added_date"].ToString());
            s_added_by = (dr["s_added_by"].ToString());
        }
        dr.Close();

Now I have all the values but to insert datetime in MySQL need to format string in yyyy-mm-dd hh:mm:ss format. To fulfil this I have code below

        s_added_date = s_added_date.Substring(0, s_added_date.Length - 3);
        DateTime datevalue = DateTime.Parse(s_added_date);
        string formatForMySql = datevalue.ToString("yyyy-MM-DD HH:MM:SS");

When I get the output I am getting 2013-11-DD 12:11:SS Not getting what's wrong here.

Please help to resolve it.

Thanks in advance.

saeed · Accepted Answer · 2013-11-16 05:24:20Z

You can change date format to this formats.In your case this could be helpful.

    // create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone

In your case:

string formatForMySql = datevalue.ToString("yyyy-MM-dd HH:mm:ss");

C Sharper · Accepted Answer · 2013-11-16 05:08:28Z

1

I thing saving it in the format : yyyy-MM-dd HH:mm:ss will help you

Try with following:

s_added_date = s_added_date.Substring(0, s_added_date.Length - 3);
        DateTime datevalue = DateTime.Parse(s_added_date);
        string formatForMySql = datevalue.ToString("yyyy-MM-dd HH:mm:ss");

answered Nov 16, 2013 at 5:08

C Sharper

8,66430 gold badges96 silver badges158 bronze badges

Comments

Sudhakar Tillapudi · Accepted Answer · 2013-11-16 05:23:46Z

1

This :

string formatForMySql = datevalue.ToString("yyyy-MM-DD HH:MM:SS");

Should be:

string formatForMySql = datevalue.ToString("yyyy-MM-dd HH:mm:ss",CultureInfo.InvariantCulture);

Explanation:

1.there is no DD format => use dd : The day of the month, from 01 through 31.

2.MM in time format is The month, from 01 through 12 ,so in time format you should use mm

3.There is no SS format use ss for seconds from 00 to 59

See Here for DatFomatting

edited Nov 16, 2013 at 5:23

answered Nov 16, 2013 at 5:10

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

Collectives™ on Stack Overflow

Converting DateTime of SQLServer to MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related