how to give date format for datacolumn of datatable in asp.net

Question

I am saving csv file data in sql database table using asp.net. In csv file there is date column and at the time of saving it giving error that " String was not recognized as a valid DateTime".
In CSV file Date is in "dd/MM/yyyy" and
in sql database table that date has datetime format.
In code
new DataColumn("TDATE",typeof(DateTime)),

Please help me in this

Piyush Pansuriya · Answer

Hi Sonali, You need to change the format of date as per the SQL format "yyyy-MM-dd". string x = dt.ToString( "yyyy-MM-dd" );

Jignesh Kumar · Answer

Hi Sonali, As per my opinion just read data from the excel in a DataTable with all columns of type string. Once all data in datatable then insert into your database table one by one. While insert just convert your column value to DateTime.

Nitin Sontakke · Answer

Please try to understand the concept behind this error! Because SQL Server is global product, used world-wide, it cannot be biased towards any culture specific date format. For example, in India, United Kingdom, Pakistan, date format is dd-mm-yyyy. In United States, date format is mm-dd-yyyy. As such, SQL Server (and any program for that matter) prefers the universally acceptable format which is not open for misunderstanding. That format is yyyy-mm-dd. Bottom line, while showing date to users, use dd-mm-yyyy format. While communicating with SQL Server use yyyy-mm-dd. Several conversion functions are avaiable both in programming languages and Transact-SQL to perform these transformations. Hope it helps and you understand.

Sachin Singh · Answer

DataTable dt = new DataTable(); DataColumn dc = new DataColumn(); dc.ColumnName = "colName" ; dc.DataType = typeof (DateTime); dt.Columns.Add(dc); DataRow dr = dt.NewRow(); dr[ "dCurrantDate" ] =dt.ToString( "yyyy-MM-dd" );; dt.Rows.Add(dr);`