title: CASE with ORDER BY causes "Operand type clash: float is incompatible with date" T-SQL
body: <p>Here's the SQL:</p>
  
  <pre><code>DECLARE @facOrGroup varchar(max) = '2,3,4,5,6',
      @sDate date = '11/01/2017',
      @eDate date = '11/01/2018',
      @orderBy varchar(40) = 'Facility Code',
      @orderDir varchar(4) = 'asc'
  
  SELECT
      f.facCode as [Facility Code]
      ,f.facName as Facility
      ,convert(varchar(10),p.[period],101) as [Period]
      ,p.[provnum] as [Medicare CCN]
      ,p.overall_rating as [Overall Rating]
      ,p.quality_rating as [Inspection Rating]
      ,p.staffing_rating as [Staffing Rating]
      ,p.rn_staffing_rating as [RN Staffing Rating]
  FROM Director.dbo.cmsProviderInfo p
  INNER JOIN dbo.facInfo f
      ON
          f.medicareCCN=p.provNum
  WHERE
      f.facID in (SELECT * FROM dbo.fn_SplitCSV(@facOrGroup)) AND
      period between @sDate and @eDate
  ORDER BY
      CASE @orderDir WHEN 'asc' THEN
          CASE @orderBy
              WHEN 'Facility Code' THEN f.facCode
              WHEN 'Facility' THEN f.facName
              WHEN 'Period' THEN p.[period]
              WHEN 'Medicare CCN' THEN p.[provnum]
              WHEN 'Overall Rating' THEN p.overall_rating
              WHEN 'Inspection Rating' THEN p.quality_rating
              WHEN 'Staffing Rating' THEN p.staffing_rating
              WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating
          END
      END ASC,
      CASE @orderDir WHEN 'desc' THEN
          CASE @orderBy
              WHEN 'Facility Code' THEN f.facCode
              WHEN 'Facility' THEN f.facName
              WHEN 'Period' THEN p.[period]
              WHEN 'Medicare CCN' THEN p.[provnum]
              WHEN 'Overall Rating' THEN p.overall_rating
              WHEN 'Inspection Rating' THEN p.quality_rating
              WHEN 'Staffing Rating' THEN p.staffing_rating
              WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating
          END
      END DESC
  
  GO
  </code></pre>
  
  <p>This gives an error on the following lines:</p>
  
  <pre><code>WHEN 'Overall Rating' THEN p.overall_rating
  WHEN 'Inspection Rating' THEN p.quality_rating
  WHEN 'Staffing Rating' THEN p.staffing_rating
  WHEN 'RN Staffing Rating' THEN p.rn_staffing_rating
  </code></pre>
  
  <p>If I remove the CASE and just put in:</p>
  
  <pre><code>ORDER BY p.overall_rating ASC
  </code></pre>
  
  <p>I get the expected result:</p>
  
  <pre><code>FAC1    Facility1    02/01/2018    455895    2    4    1    1
  FAC1    Facility1    06/01/2018    455895    2    4    1    2
  FAC1    Facility1    12/01/2017    455895    2    4    1    2
  FAC1    Facility1    01/01/2018    455895    2    4    1    1
  FAC1    Facility1    03/01/2018    455895    2    4    1    2
  FAC1    Facility1    11/01/2017    455895    3    4    1    2
  FAC1    Facility1    09/01/2018    455895    3    4    2    2
  </code></pre>
  
  <p>What is it about having the CASE in there causes the error: "Operand type clash: float is incompatible with date" to come up? It works when I CAST the 4 fields as VARCHARs in the ORDER BY, but converting floats/ints to varchar does not bode well for sorting.</p>