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

nba

Dear Fast-Report team,

I'm trying to create a report with a datetimeoffset parameter (CommandParameter) from the wizard, but when I click "next" I get this error:

"Failed to convert parameter value from a Variant to a DateTimeOffset".

I read here: https://www.fast-report.com/en/forum/index....?showtopic=5726, that a default value is needed.
So I tried the following default values... with no success:
- 1/1/2000
- '1/1/2000'
- "1/1/2000"
- [Date]
- NULL
- System.DBNull.Value

From all reports I could find in "FastReport.Net\Demos\Reports", there's only one example using CommandParameter, but it's integer... not really helpful samples here.

Could you please help?

Note: I'm using version 2017.3.0

Best regards,

nba

ipong

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

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

http://sqlmag.com/sql-server/what-you-need...e-zone-function

https://docs.microsoft.com/en-us/sql/t-sql/...et-transact-sql

nba

Is that an answer?

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

ipong

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);