CODEWITHSUNDEEP
c#sqldatetime
Jims
Jims

Reputation: 11

How do i pass arbitary date format from C# to sql backend

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)

Upvotes: 1

Views: 1270

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1500805

You say:

 So I am passing that date from front C#.net, in the below format:

but I can't see any evidence that you're using any formatting at all - and indeed you shouldn't. A DateTime doesn't have any inherent format, any more than a number does. Just as you can view the same number in hex or decimal, you can view the same date and time in various ways.

You're already passing the DateTime to the database directly rather than in a particular format, which is correct (normally questions about date/time formatting and databases are about people not using parameterized queries). You shouldn't need to worry about the formatting at all.

Possibilities:

  • Your data is getting corrupt elsewhere, and you've actually got a date before 1753
  • Your SQL statement is using the wrong value
  • There's something wrong with your database driver (you haven't said which database you're using; is it SQL Server?)

Upvotes: 2

Darin Dimitrov
Darin Dimitrov

Reputation: 1038890

Try passing a DateTime instance directly as parameter:

Cmd.Parameters.AddWithValue("@TranSactionDate", DateTime.Now);

Upvotes: 0

Related Questions