Change all values in a datatable column of type Int64 to type Datetime in C#

Question

I have a datatable with four columns and n rows. In the first column I have only integers (Int64) who represent a datetime. In the other three columns I have strings.

I only want to Change the integer values in the first column to a Datetime in the following Format (yyyy-MM-ddTHH:mm:ss.fffffff).

I attached what I tried to do. But I got a exception, that it is not possible to convert Int64 to DateTime.

So this is what I Need: 1.How to Loop through the first column. 2. How to Change/convert each entry in the first column from Int64 to DateTime

Your int64 column store ticks, so you can use `DateTime dateTime = new DateTime(ticks);`. Add your code what you try so far. — M. Wiśnicki, Dec 15 '16 at 07:56
How are the string columns relevant for problem you need help with? It seems you need help with turning a Int64 to DateTime in C# and how to access database in C#? That's two entirely different questions. Consider writing your question as a minimal, complete example for the problem.. — Imre Pühvel, Dec 15 '16 at 09:37

score 0 · Answer 1 · answered Dec 15 '16 at 07:56

0

Loop DataTable.Rows Get Rows[i][0] Set as new DateTime(long.Parse("Your integer"))

answered Dec 15 '16 at 07:56

henry

41
2

score 0 · Answer 2 · answered Dec 15 '16 at 08:03

Your int64 column store ticks you can simple convert it to DateTime type using DateTime dateTime = new DateTime(ticks);, so using foreach loop you can do that like

DataTable table = GetTable(); // Get the data table.
foreach (DataRow row in table.Rows) // Loop over the rows.
{
  var dt = new DateTime(long.Parse(row["ColumnName"].ToString())); // parse to datetime 
}

score 0 · Answer 3 · answered Dec 15 '16 at 08:06

Convert ticks to DateTime like this:

foreach (DataRow row in table.Rows) //table is your DataTable variable.
{
    DateTime dateTime = new DateTime(long.Parse(row["Date"].ToString()));
    row["Date"] = dateTime.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");
}

but, if your "Date" column has type of long, you won't be able to insert DateTime or string type inside.

Alternatively, you can use column index instead of column name, like this (assuming column with ticks is index 0):

foreach (DataRow row in table.Rows)
{
    DateTime dateTime = new DateTime(long.Parse(row[0].ToString()));
    row[0] = dateTime.ToString("yyyy-MM-ddTHH:mm:ss.fffffff");
}

score 0 · Answer 4 · answered Dec 15 '16 at 08:49

Try this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("OldDate", typeof(long));
            dt.Columns.Add("ColA", typeof(string));
            dt.Columns.Add("ColB", typeof(string));
            dt.Columns.Add("ColC", typeof(string));

            dt.Rows.Add(new object[] { 636038375869883449, "b", "b", "b" });
            dt.Rows.Add(new object[] { 636038375869883450, "b", "b", "b" });
            dt.Rows.Add(new object[] { 636038375869883451, "b", "b", "b" });
            dt.Rows.Add(new object[] { 636038375869883452, "b", "b", "b" });
            dt.Rows.Add(new object[] { 636038375869883453, "b", "b", "b" });

            dt.Columns.Add("NewDate", typeof(DateTime));

            foreach (DataRow row in dt.AsEnumerable())
            {
                row["NewDate"] = DateTime.FromBinary(row.Field<long>("OldDate"));
            }
            dt.Columns.Remove("OldDate");
            dt.Columns["NewDate"].SetOrdinal(0);
        }
    }
}

this works very good. I got s.th. like this.11.07.2016 12:39:47 How can i get the time more exactly: yyyy-MM-ddTHH:mm:ss.fffffff. What is to do? — Thomas, Dec 15 '16 at 12:12
It is a formatting issue. The DateTime object has the same accuracy as original data. The formatting to string need to be changed to include the milliseconds. — jdweng, Dec 15 '16 at 12:54

score 0 · Answer 5 · answered Dec 15 '16 at 09:09

0

Suggest you to add new DateTime Column in Data table.Convert Int64 column value to DateTime and assign it as below.You can format DateTime column value to required format.

 DataTable dt = new DataTable("Customers");
 dt.Columns.Add("BirtDate", typeof(DateTime));
 dt.Columns.Add("BirtDateLong", typeof(long));

 for(int i=0;i<10;i++)
 {
     DataRow drow = dt.NewRow();
     drow["BirtDateLong"] = 636173924284229875;
     dt.Rows.Add(drow);
 }


 IEnumerable<DataRow> rows = dt.Rows.Cast<DataRow>();
 rows.ToList().ForEach(r => r.SetField("BirtDate", new DateTime(Convert.ToInt64(r["BirtDateLong"]))));

answered Dec 15 '16 at 09:09

jignesh

1,639
1
16
18

This works really good. How to get the time more exactly (yyyy-MM-ddTHH:mm:ss.fffffff) – Thomas Dec 15 '16 at 13:10
Convery Datetime value in required string format like string strDate = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss.fffffff"). – jignesh Dec 15 '16 at 13:25

Change all values in a datatable column of type Int64 to type Datetime in C#

5 Answers5