How to convert a SQL date to a DateTime?

Question

I have a column with the date type in my SQL database. How can I convert it to C# DateTime and then back again to a SQL date?

Answer 1

A sql DATE can be directly cast to a .net DateTime and vice-versa.

to get it, use the SqlDataReader.GetDatetime Method

DateTime myDate = myDataReader.GetDateTime(myColumnIndex);

to set it, simply assign it to the value of the SqlParameter and use the .Date property of the DateTime

Answer 2

c# To get date from reader

DateTime date1;
DateTime.TryParse(reader["datecolumn"], out date1);

To insert date

string date1="2013-12-12"; 
DateTime date2;
DateTime.TryParse(reader["datecolumn"],out date2);

SqlCommand cmd= new SqlCommand("Insert into table (dateColumn) Values(@date2)",connection);
cmd.Parameters.AddWithValue("@date2",date2.Date);

TryParse returns true on successful casting false otherwise.

VB

To get date from reader

Dim date1 as Date=CType(reader("dateColumn"),Date)

To insert date

 Dim date1 as String="2013-12-12" 'you can get this date from an html input of type date

 Dim cmd As New SqlCommand("Insert into table (dateColumn) Values(@date1)",connection)
 cmd.Parameters.AddWithValue("@date1",CType(date1, Date))

NOTE:Code is written in VB. You can easily write the c# equivalent of the above code. The function name remains the same in both VB and c#. Also CType is not available in c#(though its the same as explicitly casting a variable eg. date1=(DateTime)reader("dateColumn"); I would recommend using TryParse which doesn't throw any exception on unsuccesful parses/casting.

Syntax

Date.TryParse(reader("dateColumn"), date1)