Conversion failed when converting date - linq2sql ExecuteQuery

Question

Here is my code piece:

        #region Validate and prepare parameters
        if (month > 12)
        {
            throw new ArgumentException("Value of 'month' could not be greater than 12.");
        }

        int yearEnd = year;
        int monthEnd = 0;
        if (month != 12)
        {
            monthEnd = month + 1;
        }
        else
        {
            monthEnd = 1;
            yearEnd = year + 1;
        }
        #endregion

        MyModelDataContext context = new MyModelDataContext();

        string sql =
            @"select SUM(ORDERQTY * MULTIPLIER) AS VOL_USD
                from Executions with (nolock)
                where TRANSACTTIME >= '{0}-{1}-01 00:00:00'
                    and TRANSACTTIME < '{2}-{3}-01 00:00:00'
                    and MTCONTEXT in (5,6)
                    and ORDERQTY > 0
                    AND SOURCE = 'INTMT'
                    and LEFT(SYMBOL, 3) = 'USD'";

        decimal usd___Sum = context.ExecuteQuery<decimal>(sql, year, month, yearEnd, monthEnd).First();

I'm getting the exception:

Conversion failed when converting date and/or time from character string.

when I call ExecuteQuery method. The value of year is 2013 and the value of month is 9. What am I doing wrong?

Thanks in advance.

what are the values of `year, month, yearEnd, monthEnd` when you execute the query? — Ovidiu, Nov 07 '13 at 09:06

Panagiotis Kanavos · Accepted Answer · 2013-11-07T11:26:47.330

You are comparing strings to dates, forcing the database to 'guess' what the proper format is. The format you use is not the ISO format, so the database assumes you use the format that matches its collation. I'll bet you use LATIN1 or something similar.

Instead of passing integers, then converting them to strings eg in TRANSACTTIME >= '{0}-{1}-01 00:00:00', just pass the dates:

 var startDate=new DateTime(year,month,1);
 var endDate=new DateTime(yearEnd,monthEnd,1);

 string sql =
        @"select SUM(ORDERQTY * MULTIPLIER) AS VOL_USD
            from Executions with (nolock)
            where TRANSACTTIME >= {0}
                and TRANSACTTIME < {1}
                and MTCONTEXT in (5,6)
                and ORDERQTY > 0
                AND SOURCE = 'INTMT'
                and LEFT(SYMBOL, 3) = 'USD'";

decimal usd___Sum = context.ExecuteQuery<decimal>(sql, startDate,endDate).First();

UPDATE

As Ovidiu suggests, parameter substitution doesn't work inside strings so '@p0-@p1-01' will remain unchanged even when we supply values for the @p0, @p1 parameters. We'll need to create the date outside the string by concatenating each part.

In SQL Server 2012 we have another option with DATETIMEFROMPARTS. We can create the date from its parts, eg:

 string sql =
        @"select SUM(ORDERQTY * MULTIPLIER) AS VOL_USD
            from Executions with (nolock)
            where TRANSACTTIME >= DATETIMEFROMPARTS({0},{1},1,0,0,0,0)
            ..."

although passing the actual date is still preferable

@anilca Using DateTime instead of string is the solution in this case, but the explanation as why you should do it is not the correct one. — Ovidiu, Nov 07 '13 at 10:07
@Ovidiu you should never compare strings to field values of other types, if you want to avoid internationalization and performance errors — Panagiotis Kanavos, Nov 07 '13 at 10:35

Ovidiu · Answer 2 · 2013-11-07T11:26:52.773

The problem is the way context.ExecuteQuery sends the query to the database. Although it uses arguments like {0}, similar to string.Format, it doesn't actually replace those arguments with string values, but sends them as parameters to DB. It's a bit confusing.

If you open SQL Profiler, you'll see that your query translates to something like

exec sp_executesql 
    N'select SUM(ORDERQTY * MULTIPLIER) AS VOL_USD
    from Executions with (nolock)
    where TRANSACTTIME >= ''@p0-@p1-01 00:00:00''
    and TRANSACTTIME < ''@p2-@p3-01 00:00:00'''
,N'@p0 int,@p1 int,@p2 int,@p3 int'
,@p0=2013,@p1=9,@p2=2013,@p3=10

Which will throw an exception in SQL because the parameter @p0 appears inside single quotes and is thus interpreted as the string "@p0" and not replaced with the actual value of 2013. So, instead of '2013-09-01 00:00:00' you end up with '@p0-@p1-01 00:00:00'

Sending DateTime objects instead of strings would be a better solution

where TRANSACTTIME >= {0} and and TRANSACTTIME < {1}
...
context.ExecuteQuery<decimal>(sql, new DateTime(year, month, 1), new DateTime(yearEnd, monthEnd, 1))

But if you still want to keep the current construction, you'll have to keep in mind that argument {0} becomes parameter @p0 in SQL and use something like this

where TRANSACTTIME >= convert(datetime, {0} + '-' + {1} + '-01 00:00:00')
  and TRANSACTTIME < convert(datetime, {2} + '-' + {3} + '-01 00:00:00')
...
context.ExecuteQuery<decimal>(sql, year.ToString(), month.ToString(), yearEnd.ToString(), monthEnd.ToString())

The resulting string is the same. What makes you think that this will work? The string STILL is not ISO and it will STILL be converted using the locale that corresponds to the database's collation. — Panagiotis Kanavos, Nov 07 '13 at 10:35
@PanagiotisKanavos This will not throw a `Conversion failed when converting date and/or time from character string.` error. And the string is not the same. The string sent to DB by the OP is not `'2013-09-01 00:00:00'` but `'@p0-@p1-01 00:00:00'` which is not a valid date, regardless of the collation. — Ovidiu, Nov 07 '13 at 10:41
If you want to say that parameter substitution doesn't work inside strings, you would be right. That's hard to understand from the way you wrote the answer though. Perhaps you should edit it? — Panagiotis Kanavos, Nov 07 '13 at 11:21

Conversion failed when converting date - linq2sql ExecuteQuery

2 Answers2