DataTable Convert column to Date datatype

[Cameronsmith63]

So I have quite a problem over here and its wrecking my brain.

I am reading in information from a csv file. the contents (or a row to be specific) of the file looks like:
1763275,95603462,NDBKPOS 1102I,EFT930G_16,CONNECTNET SIM,ACTIVE,RESTAURANT,RFU,2014/01/30 10:15:11,RFU,RFU,UN,

In the above row you will see a date. When filling my datatable, this date is converted to this type of format:
2012/08/19 08:55:50 PM

How can I ensure that my datatable does not change the format? I need it to be exactly the same as the date that I first specified? 2014/01/30 10:15:11, or even this: 2014-04-07 16:24:55.342123

using (OleDbCommand cmd = new OleDbCommand(string.Format
                   ("SELECT * FROM " + SourceFile + ""), con))
                {
                    con.Open();

                   using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        using (DataTable tbl = new DataTable("MyTable"))
                        {
                            adp.Fill(tbl); 
                            List<string> CAMSMerchantNumber = tbl.AsEnumerable().Select(x => x[0].ToString().PadLeft(10, '0')).ToList();// pad values and store it in a list
                            List<string> TerminalNumber = tbl.AsEnumerable().Select(x => x[1].ToString().PadLeft(10, '0')).ToList();// pad values and store it in a list
                            List<string> TerminalSoftwareVersion = tbl.AsEnumerable().Select(x => x[2].ToString().PadLeft(30, '0')).ToList();
                            List<string> TerminalType = tbl.AsEnumerable().Select(x => x[3].ToString().PadLeft(15, '0')).ToList();
                            List<string> TerminalModemType = tbl.AsEnumerable().Select(x => x[4].ToString().PadLeft(20, '0')).ToList();
                            List<string> TerminalStatus = tbl.AsEnumerable().Select(x => x[5].ToString().PadLeft(10, '0')).ToList();
                            List<string> MerchantCategory = tbl.AsEnumerable().Select(x => x[6].ToString().PadLeft(15, '0')).ToList();
                            List<string> LastUsedCommsType = tbl.AsEnumerable().Select(x => x[7].ToString().PadLeft(10, '0')).ToList();
                            List<string> TerminalLastTimeConnected = tbl.AsEnumerable().Select(x => x[8].ToString()).ToList();

[ddanbe]

Perhaps use ToString(aformat) ?

[Cameronsmith63]

Nope, doesn't work. I tried this as well:

using (OleDbCommand cmd = new OleDbCommand(string.Format
                   ("SELECT [CAMS Merchant Number], [Terminal Number], [Terminal Software Version], [Terminal Type], [Terminal Status], "
                   + " [Merchant Category], [Last used Comms Type], CONVERT(VARCHAR(10),[Terminal Last Time Connected],110) , [Serial Number of Modem], "
                   + "[Current Comms Provider], [Terminal Location] FROM " + SourceFile + ""), con))

And the error that I get is:
Undefined function 'CONVERT' in expression.

I also tried:

 CSTR([Terminal Last Time Connected]) 

And got this error:
The provider could not determine the String value. For example, the row was just created, the default for the String column was not available, and the consumer had not yet set a new String value.

[Cameronsmith63]

Nailed It. If anyone wants to use my code. Instead of trying to change the data type of a column in a data table, rather:

 using (OleDbConnection con =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + SourceDirectory + ";"
                                  + "Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
                                  {
                using (OleDbCommand cmd = new OleDbCommand(string.Format
                   ("SELECT * FROM " + SourceFile + ""), con))
                {
                    con.Open();

                    // Bind data to tbl(datatable), store all data in a list using Lambda Expressions, write data to a new CSV file. 
                    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
                    {
                        using (DataTable tbl = new DataTable("MyTable"))
                        {
                            adp.Fill(tbl); // bind data to a datatable to keep it in memory for faster execution/processing

                            List<string> CAMSMerchantNumber = tbl.AsEnumerable().Select(x => x[0].ToString().PadLeft(10, '0')).ToList();// pad values and store it in a list
                            List<string> TerminalNumber = tbl.AsEnumerable().Select(x => x[1].ToString().PadLeft(10, '0')).ToList();// pad values and store it in a list
                            List<string> TerminalSoftwareVersion = tbl.AsEnumerable().Select(x => x[2].ToString().PadLeft(30, '0')).ToList();
                            List<string> TerminalType = tbl.AsEnumerable().Select(x => x[3].ToString().PadLeft(15, '0')).ToList();
                            List<string> TerminalModemType = tbl.AsEnumerable().Select(x => x[4].ToString().PadLeft(20, '0')).ToList();
                            List<string> TerminalStatus = tbl.AsEnumerable().Select(x => x[5].ToString().PadLeft(10, '0')).ToList();
                            List<string> MerchantCategory = tbl.AsEnumerable().Select(x => x[6].ToString().PadLeft(15, '0')).ToList();
                            List<string> LastUsedCommsType = tbl.AsEnumerable().Select(x => x[7].ToString().PadLeft(10, '0')).ToList();
                            List<string> TerminalLastTimeConnected = tbl.AsEnumerable().Select(x => x[8].ToString().Replace("AM", "").Replace("PM", "")).ToList();
                            List<string> SerialNumberofModem = tbl.AsEnumerable().Select(x => x[9].ToString().PadLeft(10, '0')).ToList();
                            List<string> CurrentCommsProvider = tbl.AsEnumerable().Select(x => x[10].ToString().PadLeft(10, '0')).ToList();
                            List<string> TerminalLocation = tbl.AsEnumerable().Select(x => x[11].ToString().PadLeft(10, '0')).ToList();
                            List<string> TerminalLastConnectedModifiedDate = new List<string>();

                            for (int i = 0; i < TerminalLastTimeConnected.Count; i++)
                            {
                                try
                                {
                                    TerminalLastConnectedModifiedDate.Add(DateTime.Parse(TerminalLastTimeConnected[i]).ToString("yyyy-MM-dd hh:mm:ss.ffffff"));
                                }
                                catch
                                {
                                    TerminalLastConnectedModifiedDate.Add("");
                                }
                            }

                            index = 0;
                            StringBuilder sb = new StringBuilder();
                            sb.Append("HD" + "|" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.ffffff") + Environment.NewLine);
                            foreach (string part in CAMSMerchantNumber)
                            {// write the relevant information to the result file.
                                sb.Append("DT" + "|" + CAMSMerchantNumber[index] + '|' + TerminalNumber[index] + '|' + TerminalSoftwareVersion[index] + '|'
                                   + TerminalType[index] + '|' + TerminalModemType[index] + '|' + TerminalStatus[index] + '|'
                                   + MerchantCategory[index] + '|' + LastUsedCommsType[index] + '|' + TerminalLastConnectedModifiedDate[index] + '|' + SerialNumberofModem[index] + '|'
                                   + CurrentCommsProvider[index] + '|' + TerminalLocation[index] + Environment.NewLine);
                                index++;
                            }
                            sb.Append("TR" + "|" + index.ToString().PadLeft(10, '0') + Environment.NewLine);
                            File.WriteAllText(DestinationFile + DateTime.Now.ToString("yyyy-MM-dd") + ".dat", sb.ToString());
                        }
                    }
                }// task complete
                con.Close();

[Cameronsmith63]

something strange going on now. I run this application on my machine and the result of the date is: 2012-08-19 08:55:50. This is what I want.

When running it on the other server, I get: 8-19-2012 8:55:50

[Cameronsmith63]

regional settings in Windows is the culpurate....

Fix:

TerminalLastConnectedModifiedDate.Add(DateTime.Parse(TerminalLastTimeConnected[i]).**ToLocalTime()**.ToString("yyyy-MM-dd hh:mm:ss"));