'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 'Invo开发者_Python百科iceValue', 
    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'.

Any ideas?

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.

Don't you need an "END" after your THEN TripDate ?

All the options in the CASE statement have to be the same datatype. Is LegacyID a char?

You'd have this problem anywhere, not just in an order by. If you do CASE WHEN 'x' THEN (some int) WHEN 'y' THEN (some date) and SQL cannot do an implicit conversion of all values, then you're toast.

If you're doing a cased ordering, then the data types have to be compatible with each other.

try this for the date:

Convert(int, TripDate , 112) 

A format of 112 will give you yyyymmdd, which is useful in ordering by date as an integer. Use 1 - [date] for descending.

This is assuming that the LegacyID is an integer. Otherwise, you can try casting the date to a type SQL_VARIANT