'Order By ' SQL issue (out of range date/time value)
I'm having an issue with the below query:
SELECT Consignments.LegacyID, Consignments.TripDate, Consignments.CollectionName, CASE WHEN Sage2.InvoiceSummaryType = 'HT' THEN DeliveryTown ELSE DeliveryName + ', ' + DeliveryTown + ', ' + DeliveryPostCode END AS 'DeliveryName', Consignments.Pallets, Consignments.Weight, Consignments.BaseRate, Consignments.FuelSurcharge, Consignments.AdditionalCharges, Consignments.BaseRate * Consignments.Quantity AS 'InvoiceValue', Consignments.InvoiceNumber, Consignments.Customer FROM Consignments INNER JOIN SageAccount ON Consignments.Customer = SageAccount.LegacyID AND SageAccount.Customer = 'true' LEFT OUTER JOIN SageAccount AS Sage2 ON SageAccount.InvoiceAccount = Sage2.LegacyID WHERE (Sage2.Customer = 'true') AND (Consignments.Customer = @Customer) AND (Consignments.InvoiceNumber IS NOT NULL) OR (Sage2.Customer = 'true') AND (Consignments.InvoiceNumber IS NOT NULL) AND (Sage2.InvoiceAccount = @Customer) ORDER BY CASE WHEN Sage2.InvoiceSummaryType = 'HR' THEN TripDate WHEN Sage2.InvoiceSummaryType = 'HS' THEN Consignments.LegacyID END
For some reason, it keeps giving me the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value order by
But only when it tries to Order By TripDate, i.e. when the case 'HR' happens. TripDate is a 'datetime field'.
Having just read the question again I can't explain the specific symptons you are getting without seeing the execution plan (I would have expected HS to cause the problem). Generally though you should avoid mixing datatypes in CASE expressions as below as it simply doesn't work select case when 1=0 then GETDATE() else 'foo' end will fail as it tries to convert the string to datetime
ORDER BY CASE WHEN Sage2.InvoiceSummaryType = 'HR' THEN TripDate WHEN Sage2.InvoiceSummaryType = 'HS' THEN Consignments.LegacyID END
To get around this you can use cast(TripDate as float) - assuming (perhaps incorrectly) that the ID field is numeric or use this idiom.
ORDER BY CASE WHEN Sage2.InvoiceSummaryType = 'HR' THEN TripDate ELSE NULL END, CASE WHEN Sage2.InvoiceSummaryType = 'HS' THEN Consignments.LegacyID ELSE NULL END
You would need to check the execution plans for performance comparisons.