sql server - 'Order By ' SQL issue (out of range date/time value) -

i'm having issue below query:

select      consignments.legacyid,      consignments.tripdate,     consignments.collectionname,      case          when sage2.invoicesummarytype = 'ht' deliverytown          else deliveryname + ', ' + deliverytown + ', ' + deliverypostcode end 'deliveryname',      consignments.pallets,      consignments.weight,      consignments.baserate,      consignments.fuelsurcharge,      consignments.additionalcharges,      consignments.baserate * consignments.quantity 'invoicevalue',      consignments.invoicenumber,      consignments.customer       consignments       inner join sageaccount          on consignments.customer = sageaccount.legacyid          , sageaccount.customer = 'true'       left outer join sageaccount sage2          on sageaccount.invoiceaccount = sage2.legacyid       (sage2.customer = 'true')      , (consignments.customer = @customer)      , (consignments.invoicenumber not null)      or (sage2.customer = 'true')      , (consignments.invoicenumber not null)      , (sage2.invoiceaccount = @customer)    order      case          when sage2.invoicesummarytype = 'hr' tripdate           when sage2.invoicesummarytype = 'hs' consignments.legacyid      end 

for reason, keeps giving me following error:

the conversion of char data type datetime data type resulted in out-of-range datetime value order by

but when tries order tripdate, i.e. when case 'hr' happens. tripdate 'datetime field'.

any ideas?

having read question again can't explain specific symptons getting without seeing execution plan (i have expected hs cause problem). though should avoid mixing datatypes in case expressions below doesn't work select case when 1=0 getdate() else 'foo' end fail tries convert string datetime

order          case                   when sage2.invoicesummarytype = 'hr'                   tripdate                   when sage2.invoicesummarytype = 'hs'                   consignments.legacyid          end 

to around can use cast(tripdate float) - assuming (perhaps incorrectly) id field numeric or use idiom.

order          case                   when sage2.invoicesummarytype = 'hr'                   tripdate                   else null          end,          case                   when sage2.invoicesummarytype = 'hs'                   consignments.legacyid                   else null          end 

you need check execution plans performance comparisons.