How do i pass arbitary date format from C# to sql backend
- by Jims
I have a datetime field for the transaction date in the back end. So I am passing that date from front C#.net, in the below format:
2011-01-01 12:17:51.967
to do this I have written:
presentation layer:
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);
PropertyClass prp=new PropertyClass();
Prp.TransDate=Convert.ToDateTime(date);
PropertyClass structure:
Public class property
{
private DateTime transdate;
public DateTime TransDate
{
get
{
return transdate;
}
set
{
transdate = value;
}
}
}
From DAL layer passing the TransactionDate like this:
Cmd.Parameters.AddWithValue("@TranSactionDate”, SqlDbType.DateTime).value=propertyobj.TransDate;
While debugging from presntation layer:
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);
in this I am getting correct expected date format, but when debugs goes to this line
Prp.TransDate=Convert.ToDateTime(date);
again date format changing to 1/1/2011.
But my backend sql datefield wants the date paramter 2011-01-01 12:17:51.967 in this format otherwise throwing exception invalid date format.
Note: While passing date as string without converting to datetime getting exceptions like:
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
