Solved

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

Posted on 2009-07-13
2
161 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:bschave2
2 Comments
 
LVL 5

Accepted Solution

by:
Pablissimo earned 500 total points
ID: 24841082
Any joy with something like:

val12 = ( (DateTime) myDataRow["Lastdaysapactivity"] ).ToString( "yyyy-mm-dd hh:mm:ss t" );

?
0
 
LVL 4

Expert Comment

by:fsouzabrasil
ID: 24842023
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

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question