CODEWITHSUNDEEP
c#linqdatetimelinq-to-sqlexecutequery
xkcd
xkcd

Reputation: 2590

Conversion failed when converting date - linq2sql ExecuteQuery

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.

Upvotes: 1

Views: 1066

Answers (2)

Ovidiu
Ovidiu

Reputation: 1407

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

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

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

Upvotes: 3

Related Questions