0

I have the following query in C# using FluentData ORM.

List<dynamic> results = 
   Context().Sql(@"SELECT DISTINCT
                      a.EnteredDate,
                      bb.PK_EmployeeName,
                      bb.EmployeeId,
                      bb.EmployeeName,
                      dd.PK_EquipmentName,
                      dd.EquipmentId,
                      dd.EquipmentName
                   FROM 
                      dbo.PIT_Inspection a
                   INNER JOIN 
                      dbo.PIT_EmployeeName bb ON a.FK_EmployeeName = bb.PK_EmployeeName
                   INNER JOIN 
                      dbo.PIT_EquipmentName dd ON a.FK_EquipmentName = dd.PK_EquipmentName
                   WHERE 
                      CAST(a.EnteredDate AS DATE) BETWEEN '@0' AND @1'", 
                  fromDate, toDate ).QueryMany<dynamic>();

The parameters fromDate and toDate are strings and come populated with the following:

  • fromDate = "20150224"
  • toDate = "20150227"

The area that seems to give me problem is:

WHERE CAST(a.EnteredDate AS DATE) BETWEEN '@0' AND '@1'", 
   fromDate , toDate

I'm receiving an error

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

The above line, a.EnteredDate is of type DateTime. The query I'm executing, if copied over to SQL Server Management Studio, it runs fine. I double checked that my parameter is indeed bringing in correct data, as string.

Any idea on what causes this error?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dayan
  • 7,634
  • 11
  • 49
  • 76
  • Get rid of the '' around @0 and @1. The orm will use a SqlParameter object to represent @0 and @1 and will automatically do that for you. That applies to all parameters like that, regardless of type. – Ryan Mann Feb 19 '15 at 19:57
  • Secondly you have a typo in your query "'@0' AND @1'", you have a closing apostrophe after @1 but no opening apostrophe before @1. But you don't need the apostrophes at all. – Ryan Mann Feb 19 '15 at 19:59
  • @Ryios Thank you, your first comment helped me fix the problem. Didn't know that it will automatically handle that for me. Note on your second comment - Look at my first code example, i do have an opening double quote. Either way, you should put this as answer for me to accept. It may help others. – Dayan Feb 19 '15 at 20:05
  • My second comment was refering to this line BETWEEN '@0' AND @1' where the apostrophe (not double quote) is missing from the start of @ 1' It's hard to see the apostrophes in my comment, they are crammed up next to the double quotes. – Ryan Mann Feb 19 '15 at 20:56
  • Oh I see what you were referring to, it was correct on my code before I made the changes you recommended. I must've erased it by mistake when copying it over here. – Dayan Feb 19 '15 at 21:05

1 Answers1

2

Most ORM's convert @ parameters into SqlParameter objects, which handle typing for you so you don't need to enclose your query variables in apostrophes to denote a string.

So change

'@0' AND @1'

to

@0 AND @1

This likely applies to FluentData, and definitely applies to PetaPoco, and EntityFramework.

Ryan Mann
  • 5,178
  • 32
  • 42