title: oracle: sort by date and timestamp
body: <p>I select some data from three tables using <code>union</code> then I would like to sort them by date and timestamp (descending): </p>
  
  <pre><code>select reportname as name, convert_to_char(a.date) AS created,
  ....
  from table1 
  
  UNION
  select reportname as name, convert_to_char(a.date) AS created,
  ....
  from table2
  
  UNION
  select reportname as name, convert_to_char(a.date) AS created,
  ....
  from table3
  
  order by created desc;
  </code></pre>
  
  <p>convert_to_char is implemented as follow: </p>
  
  <pre><code>create or replace function convert_to_char(myDate date) 
  RETURN varchar IS
  BEGIN
  return TO_CHAR(cast (myDate as timestamp) at local, 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
  END;
  </code></pre>
  
  <p>the result of the sorted data I get looks like this: </p>
  
  <pre><code>created
  ------------------
  2017-04-12 16:07:07 +02:00
  2017-04-13 09:00:01 +02:00
  2017-04-13 09:00:40 +02:00
  2017-04-12 16:06:17 +02:00
  2017-04-12 16:08:37 +02:00
  2017-04-12 16:07:31 +02:00
  2017-04-13 09:00:25 +02:00
  2017-04-13 09:00:25 +02:00
  2017-04-12 16:09:07 +02:00
  2017-04-12 16:08:20 +02:00
  2017-04-12 16:08:06 +02:00
  2017-04-12 16:06:48 +02:00
  </code></pre>
  
  <p>But I expect the following sorting result: </p>
  
  <pre><code>created
  ------------------
  2017-04-13 09:00:40 +02:00
  2017-04-13 09:00:25 +02:00
  2017-04-13 09:00:25 +02:00
  2017-04-13 09:00:01 +02:00
  2017-04-12 16:09:07 +02:00
  2017-04-12 16:08:37 +02:00
  2017-04-12 16:08:20 +02:00
  2017-04-12 16:08:06 +02:00
  2017-04-12 16:07:31 +02:00
  2017-04-12 16:07:07 +02:00
  2017-04-12 16:06:48 +02:00
  2017-04-12 16:06:17 +02:00
  </code></pre>
  
  <p>any idea how to get the expected sorting result?</p>
  
  <p>thank you in advance.</p>