CommandParameter: Failed to convert parameter value from a Variant to a DateTimeOffset

Comments

• ipong

  May 2017 edited May 2017

  use yyyy-MM-dd , just like in sql server management studio
  
  date:
  <CommandParameter Name="Parameter" DataType="7" DefaultValue="1998-12-31"/>
  
  datetime:
  <CommandParameter Name="Parameter" DataType="7" DefaultValue="1998-12-31 00:00:00"/>
• nba

  May 2017 edited 3:27PM

  Thank you, but I'm asking for DateTimeOffset, i.e. DataType="34" (DataType="7" stands for Image, not Date, nor DateTime).
  
  I tried ISO8601 format (yyyy-mm-ddThh:mi:ss.mmmZ) or any other SSMS formats, but it simply doesn't work.
  
  By the way with Date or DateTime any formats works (like 01/01/2000)...
  
  Any idea?
  
  
• ipong

  May 2017 edited May 2017

  http://sqlmag.com/sql-server/what-you-need...e-zone-function
  
  https://docs.microsoft.com/en-us/sql/t-sql/...et-transact-sql
• nba

  May 2017 edited 3:27PM

  Is that an answer?
  
  Did you try to create a DateTimeOffset CommandParameter with a DefaultValue from the wizard and click "Next"?
  
  

  FRDesignerWizard.png 12.8K
• ipong

  May 2017 edited May 2017

  it must be bug.......i tried from c# project and no problem.
  
  if you insist the wizard, use varchar datatype and defaultvalue = 2017-12-31 12:35:29.1234567 +12:15
  
  

  using (SqlConnection cnn = new SqlConnection(connectionString))
  {
      cnn.Open();
  
      using (SqlCommand cmd = new SqlCommand())
      {
          cmd.Connection = cnn;
          cmd.CommandType = CommandType.Text;
          cmd.CommandText = "SELECT * FROM Nota WHERE NotaDate < @date";
                          
          SqlParameter param = new SqlParameter() {ParameterName = "@date", SqlDbType = SqlDbType.DateTimeOffset, Value = "2017-12-31 12:35:29.1234567 +12:15"};
          cmd.Parameters.Add(param);
  
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
              int count = 0;
              while (dr.Read())
              {
                  count++;
              }
              MessageBox.Show(count.ToString());                            
          }
      }
  }
  

  
  or

  DateTimeOffset dto;
  DateTimeOffset.TryParse("2017-12-31 12:35:29.1234567 +12:15", out dto);
  SqlParameter param = new SqlParameter() {ParameterName = "@date", SqlDbType = SqlDbType.DateTimeOffset, Value = dto};
  cmd.Parameters.Add(param);