spool date_format.lst
set echo on;

drop table dat2 purge;

CREATE TABLE dat2 
  ( 
     mod_date DATE, 
     am_pm    VARCHAR2 (10) 
  ); 

INSERT INTO dat2 
            (mod_date, 
             am_pm) 
VALUES      ( To_date('06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
             '7:25AM'); 

INSERT INTO dat2 
            (mod_date, 
             am_pm) 
VALUES      ( To_date('06/13/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
             '12:00AM'); 

INSERT INTO dat2 
            (mod_date, 
             am_pm) 
VALUES      ( To_date('05/31/1998 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 
             '2:34:00PM'); 

SELECT mod_date, 
       am_pm 
FROM   dat2; 

--SQL -->1 
SELECT To_char(mod_date, 'dd-mon-yyyy:hh:mi:ssam'), 
       To_date(mod_date, 'dd-mon-yyyy hh:mi:ss am') 
FROM   dat2; 

--SQL 2 
SELECT mod_date 
       ||' ' 
       ||am_pm 
FROM   dat2; 

--SQL -->3 
SELECT To_char(mod_date, 'dd-mon-yyyy:hh:mi:ssam'), 
       To_date(mod_date, 'dd-mon-yyyy hh:mi:ss am'), 
       To_char(To_date(mod_date 
                       ||' ' 
                       ||am_pm, 'dd-mon-yyyy hh:mi am'), 
       'dd-mon-yyyy hh:mi:ss am') 
FROM   dat2 
ORDER  BY To_char(To_date(mod_date 
                          ||' ' 
                          ||am_pm, 'dd-mon-yyyy hh:mi am'), 
          'dd-mon-yyyy hh:mi:ss am');  
spool off;

--LOG FILE

SQL> 
SQL> drop table dat2 purge;
drop table dat2 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> 
SQL> CREATE TABLE dat2
  2    (
  3  	  mod_date DATE,
  4  	  am_pm    VARCHAR2 (10)
  5    );

Table created.

SQL> 
SQL> INSERT INTO dat2
  2  		 (mod_date,
  3  		  am_pm)
  4  VALUES	 ( To_date('06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
  5  		  '7:25AM');

1 row created.

SQL> 
SQL> INSERT INTO dat2
  2  		 (mod_date,
  3  		  am_pm)
  4  VALUES	 ( To_date('06/13/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
  5  		  '12:00AM');

1 row created.

SQL> 
SQL> INSERT INTO dat2
  2  		 (mod_date,
  3  		  am_pm)
  4  VALUES	 ( To_date('05/31/1998 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'),
  5  		  '2:34:00PM');

1 row created.

SQL> 
SQL> SELECT mod_date,
  2  	    am_pm
  3  FROM   dat2;

MOD_DATE  AM_PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
--------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
05-JUN-03 7:25AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
13-JUN-03 12:00AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
31-MAY-98 2:34:00PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

SQL> 
SQL> --SQL -->1
SQL> SELECT To_char(mod_date, 'dd-mon-yyyy:hh:mi:ssam'),
  2  	    To_date(mod_date, 'dd-mon-yyyy hh:mi:ss am')
  3  FROM   dat2;

TO_CHAR(MOD_DATE,'DD-M TO_DATE(M                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
---------------------- ---------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
05-jun-2003:12:00:00am 05-JUN-03                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
13-jun-2003:12:00:00am 13-JUN-03                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
31-may-1998:12:00:00am 31-MAY-98                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

SQL> 
SQL> --SQL 2
SQL> SELECT mod_date
  2  	    ||' '
  3  	    ||am_pm
  4  FROM   dat2;

MOD_DATE||''||AM_PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
--------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
05-JUN-03 7:25AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
13-JUN-03 12:00AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
31-MAY-98 2:34:00PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

SQL> 
SQL> --SQL -->3
SQL> SELECT To_char(mod_date, 'dd-mon-yyyy:hh:mi:ssam'),
  2  	    To_date(mod_date, 'dd-mon-yyyy hh:mi:ss am'),
  3  	    To_char(To_date(mod_date
  4  			    ||' '
  5  			    ||am_pm, 'dd-mon-yyyy hh:mi am'),
  6  	    'dd-mon-yyyy hh:mi:ss am')
  7  FROM   dat2
  8  ORDER  BY To_char(To_date(mod_date
  9  			       ||' '
 10  			       ||am_pm, 'dd-mon-yyyy hh:mi am'),
 11  	       'dd-mon-yyyy hh:mi:ss am');
                       ||am_pm, 'dd-mon-yyyy hh:mi am'),
                        *
ERROR at line 5:
ORA-01855: AM/A.M. or PM/P.M. required 


SQL> spool off;