Link to home
Start Free TrialLog in
Avatar of bschave2
bschave2

asked on

how to format time from mysql to yyyy-mm-dd

I am pulling data out of mysql and inserting it into another table. The date for the lastsapactivity and applidate fields are formatting like 7/11/2009 12:00:00 am. in the db, it's formatted like 2009-07-11 and for some reason it's formatting outside of that. How do I fix it?
OdbcCommand cmd23 = new OdbcCommand("SELECT test.companycode,test.responsiblectr,test.ordernumber,test.ordertype,test.system,test.description,test.reimbursable,test.totalplan,test.totalactual,test.Classification,test.projectleader,test.shorttext,test.Lastdaysapactivity,test.applidate,test.IOPlanCIAC,test.IOPendingCIAC,test.longtxt,test.ActualPriorMonth,test.Statuspro from test LEFT JOIN projectmanager ON projectmanager.ordernumber = test.ordernumber WHERE projectmanager.ordernumber IS NULL", con22);
            OdbcDataAdapter da23 = new OdbcDataAdapter(cmd23);
            DataSet ds23 = new DataSet();
            da23.Fill(ds23, "test");
            int number;
            number = 0;
 
            foreach (DataRow myDataRow in ds23.Tables["test"].Rows)//test that if particular order  number does not exist 
            //insert that  
            {
                number = number + 1;
                string val1, val2, val3, val4, val5, val6, val7, val8, val9, val10, val11, val12, val13, val14, val15, val16, val17, val18, val19, val20;
 
                val1 = myDataRow["ordernumber"].ToString();
                val2 = myDataRow["companycode"].ToString();
                val3 = myDataRow["responsiblectr"].ToString();
                val4 = myDataRow["reimbursable"].ToString();
                val5 = myDataRow["totalplan"].ToString();
                val6 = myDataRow["totalactual"].ToString();
                val7 = myDataRow["ordertype"].ToString();
                val8 = myDataRow["system"].ToString();
                val9 = myDataRow["projectleader"].ToString();
                val10 = myDataRow["description"].ToString();
                val11 = myDataRow["shorttext"].ToString();
                val12 = myDataRow["Lastdaysapactivity"].ToString();
                val13 = myDataRow["applidate"].ToString();
                val14 = myDataRow["IOPlanCIAC"].ToString();
                val15 = myDataRow["IOPendingCIAC"].ToString();
                val16 = myDataRow["longtxt"].ToString();
                val18 = myDataRow["Classification"].ToString();
                val19 = myDataRow["ActualPriorMonth"].ToString();
                val20 = myDataRow["StatusPro"].ToString();
 
                //string date1String = Format("val2", val12);
 
 
 
                OdbcConnection con5 = new OdbcConnection(ConnStr);
                OdbcCommand cmd5 = new OdbcCommand("insert into projectmanager(ordernumber,companycode,responsiblectr,reimbursable,totalplan,totalactual,ordertype,system,projectleader,description,shorttext,Lastdaysapactivity,applidate,IOPlanCIAC,IOPendingCIAC,longtxt,Classification,ActualPriorMonth,Statuspro) values('" + val1 + "','" + val2 + "','" + val3 + "','" + val4 + "','" + val5 + "','" + val6 + "','" + val7 + "','" + val8 + "','" + val9 + "','" + val10 + "','" + val11 + "','" + myDataRow["Lastdaysapactivity"] + "','" + val13 + "','" + val14 + "','" + val15 + "','" + val16 + "','" + val18 + "','" + val19 + "','" + val20 + "')", con5);
                con5.Open();
                cmd5.ExecuteNonQuery();
                con5.Close();
                
            }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pablissimo
Pablissimo

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
see this code:
val12 = myDataRow["Lastdaysapactivity"].ToString();
string[] arrval12;
arrval12 = val12.Split('-');
val12 = arrval12[1].ToString() + "/" + arrval12[2].ToString() + "/" arrval12[0].ToString();

Open in new window