DATEPART(DAYOFYEAR) presenting errors

[jneave]

Hi, I'm trying to convert the following SQL to Linq2Sql with Linqer 3.5:

SELECT   DATEPART(DAYOFYEAR, VQ5DateTimeLoaded) AS DayOfYear,
        YEAR(VQ5DateTimeLoaded)                AS YEAR     ,
        COUNT(VQ5ID)                           AS CaseCount
FROM     [SiDem_Hackney].[DBO].[DVLA_VQ5Cases]
WHERE    VQ5NoticeNumber LIKE 'HQ%'
AND      VQ5DateTimeLoaded BETWEEN '2009-07-21 00:00:00' AND      '2009-08-07 23:59:59'
GROUP BY DATEPART(DAYOFYEAR, VQ5DateTimeLoaded),
        YEAR(VQ5DateTimeLoaded)
ORDER BY YEAR(VQ5DateTimeLoaded) DESC,
        DATEPART(DAYOFYEAR, VQ5DateTimeLoaded) DESC

And I'm getting the following error:

SQL syntax error: Invalid parameter DAYOFYEAR for function DATEPART.

The SQL works perfectly in SQL Management Studio.

Is this a limitation of Linqer? A bug?

Regards,

James.

[jneave]

Workaround:

Change the SDL to use DAY:

SELECT   DATEPART(DAY, VQ5DateTimeLoaded) AS DayOfYear,
        YEAR(VQ5DateTimeLoaded)                AS YEAR     ,
        COUNT(VQ5ID)                           AS CaseCount
FROM     [SiDem_Hackney].[DBO].[DVLA_VQ5Cases]
WHERE    VQ5NoticeNumber LIKE 'HQ%'
AND      VQ5DateTimeLoaded BETWEEN '2009-07-21 00:00:00' AND      '2009-08-07 23:59:59'
GROUP BY DATEPART(DAY, VQ5DateTimeLoaded),
        YEAR(VQ5DateTimeLoaded)
ORDER BY YEAR(VQ5DateTimeLoaded) DESC,
        DATEPART(DAY, VQ5DateTimeLoaded) DESC

Create the Linq:

from dvla_vq5cases in db.DVLA_VQ5Cases
where
 dvla_vq5cases.VQ5NoticeNumber.StartsWith("HQ") &&
 dvla_vq5cases.VQ5DateTimeLoaded >= "2009-07-21 00:00:00" && dvla_vq5cases.VQ5DateTimeLoaded <= "2009-08-07 23:59:59" &&
 dvla_vq5cases.VQ5ID != null
group dvla_vq5cases by new {
 Column1 = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.Day,
 Column2 = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.Year
} into g
orderby
 g.Key.Column2g.Key.VQ5DateTimeLoaded.Value.Year descending,
 g.Key.Column1g.Key.VQ5DateTimeLoaded.Value.Day descending
select new {
 g.Key.Column1,
 g.Key.Column2,
 CaseCount = (Int64?)g.Count()
}

Then change the Linq to user DayOfYear instead of Day.
Also noticed that it tries to compare DateTime to String, and there is no implicit cast or convert (not that it matters, it's a parameter code side.

Regards,

James.

[Mikhail Oumantsev]

1.I would say that was a limitation, I put in the first version of Linqer, but then I forgot to extend it. Thanks for the workaround. I will include DAYOFYEAR support into the next Linqer release.

2.I do need to handle date strings conversion eventually. I explained the problem at FAQ page sqltolinq.com/faq#date
Linqer spread worldwide, so many different countries have different date string formats. That's why I leave date string conversion to be done manually. Probably the solution could be found, if Linqer allowed to set a date format in options.

[jneave]

Thanks, glad I could help

The group and order by clauses went a bit wonky as well, here's what Linqer generated:

from dvla_vq5cases in db.DVLA_VQ5Cases
where
 dvla_vq5cases.VQ5NoticeNumber.StartsWith("HQ") &&
 dvla_vq5cases.VQ5DateTimeLoaded >= "2009-07-21 00:00:00" && dvla_vq5cases.VQ5DateTimeLoaded <= "2009-08-07 23:59:59" &&
 dvla_vq5cases.VQ5ID != null
group dvla_vq5cases by new {
 Column1 = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.Day,
 Column2 = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.Year
} into g
orderby
 g.Key.Column2g.Key.VQ5DateTimeLoaded.Value.Year descending,
 g.Key.Column1g.Key.VQ5DateTimeLoaded.Value.Day descending
select new {
 g.Key.Column1,
 g.Key.Column2,
 CaseCount = (Int64?)g.Count()
}

This is what I used in the end:

               var responseGraph = from dvla_vq5cases in db.DVLA_VQ5Cases
                                   where
                                     dvla_vq5cases.VQ5NoticeNumber.StartsWith("HQ") &&
                                     dvla_vq5cases.VQ5DateTimeLoaded >= now.AddDays(-14) &&
                                     dvla_vq5cases.VQ5ID != null
                                   group dvla_vq5cases by new
                                   {
                                       DayOfYear = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.DayOfYear,
                                       Year = (System.Int32?)dvla_vq5cases.VQ5DateTimeLoaded.Value.Year
                                   } into g
                                   orderby
                                     g.Key.Year descending,
                                     g.Key.DayOfYear descending
                                   select new
                                   {
                                       g.Key.Year,
                                       g.Key.DayOfYear,
                                       CaseCount = g.Count()
                                   };

Not a huge problem, but definitely (regardless of the dates) that order by clause does not compile.

Regards,

James.

[Mikhail Oumantsev]

Thanks, I will fix this mess in order by in the upcoming release.

[Mikhail Oumantsev]

Fixed in Linqer 4.0.2

[jneave]

But not in 3.5? 8'(

[Mikhail Oumantsev]

Linqer 3.5.2 update is coming soon.
This fix will be included.

[jneave]

That's fine, I was just worried you were dropping .NET 3.5 support! 8@

J.

[Mikhail Oumantsev]

Oh no! I have too many Linqer 3.5 customers and can't leave them behind.