current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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.

share|improve this question
    
@f-r-i-e-n-d-s yes sir. perfect ch ki..:) thank you. – Ashok_Karale Nov 16 '13 at 5:17
up vote 1 down vote accepted

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");
share|improve this answer
up vote 1 down vote

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");
share|improve this answer
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.