Solved

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

Posted on 2009-07-13
2
164 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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