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

Sonali Avhad

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
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);`
0
Jignesh Kumar5y ago
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.

+2
Nitin Sontakke5y ago
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.
+2
Piyush Pansuriya5y ago
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");
+2