convert DataTable entire column to YYYY/MM/DD format without for-loop from YYYY-MM-DDT00:00:00

Question

User-1971168174 posted

Hi,

Is there no way we can covert entire datatable column having datetime in the format (YYYY-MM-DDT00:00:00) to format ( YYYY/MM/DD ).

Any sample using Linq or any other way will be great help.

Thanks!

Answer 1

User-1780421697 posted

using System;
using System.Data;
using System.Linq;
using System.Data.DataSetExtensions;

public class Program
{
	public static void Main()
	{
		
               // Create a new DataTable.    
                DataTable dt = new DataTable("tbl");  
                DataColumn dtColumn;  
  
		
		dtColumn = new DataColumn();  
		dtColumn.DataType = typeof(DateTime);  
		dtColumn.ColumnName = "CreatedDate";  
		dt.Columns.Add(dtColumn);  
    
		DataRow dr = dt.NewRow();
		dr["CreatedDate"] = DateTime.Now;
		
		DataRow dr1 = dt.NewRow();
		dr1["CreatedDate"] = DateTime.Now.AddDays(1);
		
		dt.Rows.Add(dr);
		dt.Rows.Add(dr1);
		
	
		
		var query = dt.AsEnumerable()
			.Select(r => r.Field<DateTime>("CreatedDate").ToString("yyyy/MM/dd"));
		
		foreach (var r in query)
		{
			Console.WriteLine(r.ToString());
		}
		
	}
}

Output is:

2020/01/22
2020/01/23

//this is just for an idea that you can create anonymous object and then reuse it.

Answer 2

User288213138 posted

Hi NWRITER,

convert DataTable entire column to YYYY/MM/DD format without for-loop...

Can you tell me where the data comes from?

If there are in a database, you can try the following code:

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            string query = "SELECT * FROM DateTable";        
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand(query, con))
                    {
                        con.Open();
                        SqlDataReader sdr = cmd.ExecuteReader();

                        while (sdr.Read())
                        {
                            string s1 = Convert.ToString(sdr["Date"]);
                            DateTime value;
                            DateTime.TryParse(s1, out value);
                            Label1.Text = value.ToString("yyyy-MM-dd");
                        }

                    }
                }

Best regards,

Sam

Answer 3

User409696431 posted

How are you populating the DataTable?  If from an SQL Server database you can format the date in the query.  https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15

Answer 4

User-1971168174 posted

Thanks for your reply!

Data is coming from API, I need to convert entire column in datatable without using for loop.

Answer 5

User-1780421697 posted

Clearly You cannot change the DataType after the Datatable is filled with data, however there are some tech to deal with update or clone of data table. Please find the link there is an extension that do conversion for you.

https://stackoverflow.com/questions/9028029/how-to-change-datatype-of-a-datacolumn-in-a-datatable?rq=1