Programming

 SELECT
TO_CHAR(DATE)  DATE1,
INVOICE
FROM ACCOUNT1
WHERE DATE < (to_date('122003','MMYYYY'))         
UNION 
SELECT 
TO_CHAR(DATE)  DATE1,
INVOICE
FROM ACCOUNT2
WHERE DATE >= (to_date('122003','MMYYYY')) AND              
 ORDER BY 1 ASC;

DATE1               INVOICE
--------------- ---------------- 
28-APR-08                 412.82 
28-APR-09                 363.72 
28-APR-10                 578.82 
28-AUG-07                    111 
28-AUG-08                 241.05 
28-AUG-09                 363.72 
28-DEC-06                    111 
28-DEC-09                 363.72 
28-FEB-07                    111 
28-FEB-10                 294.42 
28-JAN-07                    296 
28-JAN-08                 372.83

DATE1               INVOICE
--------------- ---------------- 
28-FEB-07                    111 
28-AUG-07                    111 
28-APR-08                 412.82 
28-APR-09                 363.72 
28-APR-10                 578.82

#!/bin/sh

sed 's/\(..\)-\(...\)-\(..\).*/\3\2\1 &/' | \
	sed 's/JAN/01/; s/FEB/02/; s/MAR/03/; s/APR/04/; s/MAY/05/; s/JUN/06/; s/JUL/07/; s/AUG/08/; s/SEP/09/; s/OCT/10/; s/NOV/11/; s/DEC/12/;' | \
		sort | \
			cut -f 2- -d ' '

SQL> 
SQL> -- Check the data in my tables
SQL> --
SQL> select * from account1;

DT         INVOICE
--------- ----------
28-SEP-02     111.22
28-NOV-02     222.33

2 rows selected.

SQL> 
SQL> select * from account2;

DT         INVOICE
--------- ----------
28-FEB-08     333.44
28-AUG-09     444.55

2 rows selected.

SQL> 
SQL> --
SQL> -- The problem is that sorting is done on character strings, and
SQL> -- not on dates. ORDER BY 1 ASC means "order by 1st column in the
SQL> -- SELECT column-list", and the 1st column is a string!
SQL> --
SQL> select to_char(dt) date1, invoice
  2    from account1
  3   where dt < to_date('122003','mmyyyy')
  4  union
  5  select to_char(dt) date1, invoice
  6    from account2
  7   where dt >= to_date('122003','mmyyyy')
  8   order by 1 asc;

DATE1              INVOICE
------------------ ----------
28-AUG-09           444.55
28-FEB-08           333.44
28-NOV-02           222.33
28-SEP-02           111.22

4 rows selected.

SQL> 
SQL> --
SQL> -- SOLUTION 1: Select *dates* and sort on *dates*
SQL> --
SQL> select dt date1, invoice
  2    from account1
  3   where dt < to_date('122003','mmyyyy')
  4  union
  5  select dt, invoice
  6    from account2
  7   where dt >= to_date('122003','mmyyyy')
  8   order by 1 asc;

DATE1         INVOICE
--------- ----------
28-SEP-02     111.22
28-NOV-02     222.33
28-FEB-08     333.44
28-AUG-09     444.55

4 rows selected.

SQL> 
SQL> --
SQL> -- The only problem with SOLUTION 1 is that the dates
SQL> -- will be displayed in the default format of the client.
SQL> -- My client is sqlplus, and its default date format is
SQL> -- "DD-MON-RR", so that's how the dates are formatted for me.
SQL> 
SQL> -- Now let's say you want to sort by dates, and want to *display*
SQL> -- those dates in some non-default character format, say, "MM/DD/YYYY".
SQL> -- For that, you'll have to be explicit.
SQL> 
SQL> -- SOLUTION 2: Format *dates* as per your preference in an outer
SQL> --         SELECT query and mention the *date column* explicitly
SQL> --         in the ORDER BY clause
SQL> --
SQL> select to_char(dt, 'mm/dd/yyyy') date1, invoice
  2    from ( select dt, invoice
  3          from account1
  4             where dt < to_date('122003','mmyyyy')
  5             union
  6            select dt, invoice
  7          from account2
  8             where dt >= to_date('122003','mmyyyy')
  9          )
 10   order by dt asc;

DATE1          INVOICE
---------- ----------
09/28/2002     111.22
11/28/2002     222.33
02/28/2008     333.44
08/28/2009     444.55

4 rows selected.

SQL> 
SQL> 
SQL>