title: Sort Asc and desc in the same column
body: <p>I have a date column in a table, and I want to create a SELECT query that the <strong>result order</strong> of that query will be that all the dates that is bigger or equal to today's date will be first and the order should be ASC , and all the dates that is smaller than today's date will be second and the order will be DESC</p>
  <p>The order should look like this:</p>
  <p>notes:</p>
  <ol>
  <li>In the following list the date format is YYYY-MM-DD</li>
  <li>Today's date is 08 in Feb 2021</li>
  <li>My date's data type is <code>date</code></li>
  </ol>
  <pre class="lang-none prettyprint-override"><code>2021-02-08
  2021-02-09
  2021-05-18
  2022-06-29&lt;----This is the last bigger and equal then today
  2021-02-07&lt;----This is the first smaller then today
  2021-02-06
  2021-01-03
  2020-12-06
  2020-10-08
  </code></pre>
  <pre><code>    ;with cte
      as
      (
      select 
          CustomrId
          ,CustomrName
          ,SubscriptionDate
          ,SubscriptionInDays
          ,datediff(day, dateadd(DAY, SubscriptionInDays, SubscriptionDate), CAST(GETDATE() AS Date )) as DaysToEnd
      from 
          TBL 
  )
  select 
      CustomrId
      ,CustomrName
      ,SubscriptionDate
      ,SubscriptionInDays
      ,-iif(DaysToEnd &gt; 0, 0 ,DaysToEnd) as DaysToEnd
  from 
      cte
  order by 
          case 
              when DaysToEnd = 0 then 0
              when DaysToEnd &gt; 0 then 1 
          end
          ,DaysToEnd
  
  
  
  In this approach this is the result order I'm getting:
  2021-02-08
  2021-02-09
  2021-05-18
  2022-06-29&lt;----This is the last bigger and equal then today
  2020-10-08
  2020-12-06
  2021-01-03
  2021-02-06
  2021-02-07&lt;----This is the first smaller then today
  </code></pre>