CODEWITHSUNDEEP
c#winformsdatetime
derekantrican
derekantrican

Reputation: 2285

Problems with sending DateTime to SQL Table

I'm writing WinForms code in C# and basically have the following:

DateTime localDate = DateTime.UtcNow;
SqlConnection Conn = new SqlConnection("....[connection info]....");
Conn.Open();
SqlCommand sqlcomm = new SqlCommand("INSERT INTO dbo.Call VALUES(...., @Time,...);", Conn);

sqlcomm.Parameters.Add("@Time", SqlDbType.DateTime);
sqlcomm.Parameters["@Time"].Value = localDate;

Int32 o = sqlcomm.ExecuteNonQuery();

This throws an error of "When converting a string to DateTime, parse the string to take the date before putting each variable into the DateTime object." From what I gather, it thinks the localDate variable is a string but if I write to the console localDate.GetType() it says System.DateTime.

The column for "@Time" in the database is set up to DateTime, so that's not the issue. Any thoughts?

Upvotes: 1

Views: 560

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74355

Given this table schema:

create table dbo.call_history
(
  id          int      not null identity(1,1) primary key clustered ,
  my_date     date         null ,
  my_time     time         null ,
  my_datetime datetime     null ,
)

This code works just fine:

using ( SqlConnection conn = new SqlConnection( connectString ) )
using ( SqlCommand cmd = conn.CreateCommand() )
{

  cmd.CommandType = CommandType.Text;
  cmd.CommandText = @"
    insert dbo.call_history ( my_date , my_time , my_datetime )
    values                  ( @pDate  , @pDate  , @pDate      )

    select scope_identity()
  ";

  cmd.Parameters.AddWithValue( "@pDate" , DateTime.UtcNow );

  conn.Open();

  // double downcast required herebecause scope_identity()
  // returns numeric(38,0) which gets mapped to decimal
  int id = (int)(decimal) cmd.ExecuteScalar() ;

  conn.Close();

}

Upvotes: 0

T.S.
T.S.

Reputation: 19384

You're almost there. for Sql Server think among these lines

select cast(getdate() as time)

Problem is that in .net there is no such type as time, so you need to adapt

SqlCommand sqlcomm = new SqlCommand(
    "INSERT INTO dbo.Call VALUES(...., cast(@Time as time),...);", Conn);

sqlcomm.Parameters.AddWithValue("@Time", localDate);

this is all you should need. Although, I think, you may not even need to add cast as DB engine itself will try cast it. The problem, I think, that you explicitly said your type SqlDbType.DateTime. But if you use AddWithValue, provider will do things for you.

since @Frédéric mentioned TimeSpan, you can try this as well

sqlcomm.Parameters.AddWithValue("@Time", localDate.TimeOfDay);
' and no cast(@Time as time)

Upvotes: 2

The OrangeGoblin
The OrangeGoblin

Reputation: 794

The UTC format is not an sql date time format whuch you specified in the parameters.add

In the past I have formatted the utc time to that of yyyy-MM-dd.

Upvotes: 0

Related Questions