Home » SQL & PL/SQL » SQL & PL/SQL » date format (oracle 10g,solaris)
date format [message #588501] Wed, 26 June 2013 05:03 Go to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi ,

I want to do 'ORDER BY' by concatenating the two columns(date +varchar2).But not working and geting the error - ORA-01855: AM/A.M. or PM/P.M. required
. Please check below and provide solution also .
SQL> create table dat2 (
  mod_date  date,
  am_pm     varchar2 (10) ) ;  2    3

Table created.

SQL> 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');  2

1 row created.

SQL> 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');  2

1 row created.

SQL> 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');   2

1 row created.

SQL> select MOD_DATE,AM_PM from dat2;

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

SQL> set linesize 3000

--SQL -->1

SQL>   select to_char(MOD_DATE, 'dd-mon-yyyy:hh:mi:ssam') ,to_date(mod_date, 'dd-mon-yyyy hh:mi:ss am')
  from dat2 ;   2

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 2

SQL>  select mod_date||' '||Am_pm
 from dat2 ;    2

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


--SQL -->3

SQL>   select to_char(MOD_DATE, 'dd-mon-yyyy:hh:mi:ssam') ,to_date(mod_date, 'dd-mon-yyyy hh:mi:ss am'),
  2  to_char(  to_date(mod_date||' '||Am_pm, 'dd-mon-yyyy hh:mi am'),'dd-mon-yyyy hh:mi:ss am')
  3    --,to_char(to_date(mod_date||' '||Am_pm, 'dd-mon-yyyy hh:mi am'), 'dd-mon-yyyy hh:mi am')
  4   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');
to_char(  to_date(mod_date||' '||Am_pm, 'dd-mon-yyyy hh:mi am'),'dd-mon-yyyy hh:mi:ss am')
                                *
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required


Re: date format [message #588504 is a reply to message #588501] Wed, 26 June 2013 05:10 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
hi,

What is the purpose of the column am_pm? Why you don't define it as a DATE?

Regards,
Dariyoosh
Re: date format [message #588508 is a reply to message #588501] Wed, 26 June 2013 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please learn how to format a SQL statement, this is unreadable.
You can learn it using SQL Formatter.

Regards
Michel
Re: date format [message #588512 is a reply to message #588508] Wed, 26 June 2013 05:26 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Sorry..
PFA- formatted script and log file
Re: date format [message #588514 is a reply to message #588512] Wed, 26 June 2013 05:35 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
He meant formatted HERE on the forum page not in your file Smile

Regards,
Dariyoosh
Re: date format [message #588516 is a reply to message #588514] Wed, 26 June 2013 05:47 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
PFB. Please note in section - SQL 2 , the year formating is also not getting displayed properly.
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 

Re: date format [message #588518 is a reply to message #588514] Wed, 26 June 2013 05:48 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
your am_pm values have different formats. But really you should just drop that column and just use mod_date. All this approach will lead to is bugs and performance problems.
Re: date format [message #588519 is a reply to message #588518] Wed, 26 June 2013 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
To_date(mod_date, 'dd-mon-yyyy hh:mi:ss am')

Never to_date a date, it can corrupt your data.
Re: date format [message #588522 is a reply to message #588516] Wed, 26 June 2013 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
USE "set trimout on trimspool on" we have nothing to do with lines with 10000 spaces at the end.

Regards
Michel
Re: date format [message #588523 is a reply to message #588519] Wed, 26 June 2013 05:59 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option, but - look how ugly it is. You don't want to do that, really (even if it can be simplified).
SQL> select * from dat2;

MOD_DATE            AM_PM
------------------- ----------
05.06.2003 00:00:00 7:25AM
13.06.2003 00:00:00 12:00AM
31.05.1998 00:00:00 2:34:00PM

SQL>   select mod_date,
  2           am_pm,
  3           to_date (
  4              to_char (mod_date, 'dd.mm.yyyy') || ' '
  5              || to_char (
  6                    to_date (substr (am_pm, 1, length (am_pm) - 2), 'hh:mi:ss')
  7                    + case when substr (am_pm, -2) = 'AM' then 0 else 12 / 24 end,
  8                    'hh24:mi:ss'),
  9              'dd.mm.yyyy hh24:mi:ss')
 10              result
 11      from dat2
 12  order by to_date (
 13              to_char (mod_date, 'dd.mm.yyyy') || ' '
 14              || to_char (
 15                    to_date (substr (am_pm, 1, length (am_pm) - 2), 'hh:mi:ss')
 16                    + case
 17                         when substr (am_pm, -2) = 'AM' then 0
 18                         else 12 / 24
 19                      end,
 20                    'hh24:mi:ss'),
 21              'dd.mm.yyyy hh24:mi:ss')
 22  ;

MOD_DATE            AM_PM      RESULT
------------------- ---------- -------------------
31.05.1998 00:00:00 2:34:00PM  31.05.1998 14:34:00
05.06.2003 00:00:00 7:25AM     05.06.2003 07:25:00
13.06.2003 00:00:00 12:00AM    13.06.2003 12:00:00

SQL>
Re: date format [message #588528 is a reply to message #588523] Wed, 26 June 2013 06:31 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Thank you very much for the query ..

Can you please explain how the below line will work in above query
Re: date format [message #588531 is a reply to message #588528] Wed, 26 June 2013 06:38 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Sorry to paste the line
case when substr (am_pm, -2) = 'AM' then 0 else 1/2 end
Re: date format [message #588534 is a reply to message #588531] Wed, 26 June 2013 06:46 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
adding 1/2 to a date adds 12 hours. It's converting the time from am/pm format to 24hr format.
Re: date format [message #588535 is a reply to message #588531] Wed, 26 June 2013 06:46 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not "1/2", but "12/24". True, it is the same number, but "12/24" can be easily read as "add 12 hours (out of 24, which makes one day)". You can't read "1/2" the same way.

I used it to switch all times to HH24 format (i.e. no AM/PM).
Re: date format [message #588545 is a reply to message #588535] Wed, 26 June 2013 07:34 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Thanks for the clarification.
One scenario is failing with this query 12:30 Am will be converted as 12:30PM and vice-versa as given below. Can you please check?
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ss AM';

Session altered.


SQL> SELECT mod_date,
  2         am_pm,
  3         To_date(To_char (mod_date, 'dd.mm.yyyy')
  4                 || ' '
  5                 || To_char (To_date (Substr (am_pm, 1, Length (am_pm) - 2),
  6                             'hh:mi:ss')
  7                             + CASE WHEN Substr (am_pm, -2) = 'AM' THEN 0 ELSE 12/
  8                             24 END,
  9                       'hh24:mi:ss'), 'dd.mm.yyyy hh24:mi:ss')result
 10  FROM   dat2
 11  ORDER  BY result;

MOD_DATE               AM_PM      RESULT
---------------------- ---------- ----------------------
1998-05-31 12:00:00 AM 2:34PM     1998-05-31 02:34:00 PM
2003-06-05 12:00:00 AM 7:25PM     2003-06-05 07:25:00 PM
2003-06-13 12:00:00 AM 12PM       2003-06-13 12:00:00 AM
2003-06-13 12:00:00 AM 12:30PM    2003-06-13 12:30:00 AM
2003-06-13 12:00:00 AM 01AM       2003-06-13 01:00:00 AM
2003-06-13 12:00:00 AM 10AM       2003-06-13 10:00:00 AM
2003-06-13 12:00:00 AM 11:30AM    2003-06-13 11:30:00 AM
2003-06-13 12:00:00 AM 12:30AM    2003-06-13 12:30:00 PM
2013-06-30 12:00:00 AM 12:15PM    2013-06-30 12:15:00 AM

9 rows selected.

Re: date format [message #588546 is a reply to message #588545] Wed, 26 June 2013 07:47 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'd be because in 24hr clock the hour 12 is 12pm.

The correct solution here is to fix it so that am_pm is in a consistent format, or better yet get rid of the column entirely.
If it's not in a consistent format then bugs will be unavoidable.
Re: date format [message #588547 is a reply to message #588545] Wed, 26 June 2013 07:48 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here you go, a little bit of adjustment:
SQL> select * from dat2;

MOD_DATE            AM_PM
------------------- ----------
05.06.2003 00:00:00 7:25AM
13.06.2003 00:00:00 12:00AM
31.05.1998 00:00:00 2:34:00PM
26.06.2013 00:00:00 12:15AM

SQL>   SELECT mod_date,
  2           am_pm,
  3           TO_DATE (
  4              TO_CHAR (mod_date, 'dd.mm.yyyy') || ' '
  5              || TO_CHAR (
  6                    TO_DATE (SUBSTR (am_pm, 1, LENGTH (am_pm) - 2), 'hh:mi:ss')
  7                    + CASE
  8                         WHEN SUBSTR (am_pm, -2) = 'AM'
  9                         THEN
 10                            CASE
 11                               WHEN REGEXP_SUBSTR (am_pm, '\d+') = 12
 12                               THEN
 13                                  -12 / 24
 14                               ELSE
 15                                  0
 16                            END
 17                         ELSE
 18                            12 / 24
 19                      END,
 20                    'hh24:mi:ss'),
 21              'dd.mm.yyyy hh24:mi:ss')
 22              result
 23      FROM dat2
 24  ORDER BY TO_DATE (
 25              TO_CHAR (mod_date, 'dd.mm.yyyy') || ' '
 26              || TO_CHAR (
 27                    TO_DATE (SUBSTR (am_pm, 1, LENGTH (am_pm) - 2), 'hh:mi:ss')
 28                    + CASE
 29                         WHEN SUBSTR (am_pm, -2) = 'AM'
 30                         THEN
 31                            CASE
 32                               WHEN REGEXP_SUBSTR (am_pm, '\d+') = 12
 33                               THEN
 34                                  -12 / 24
 35                               ELSE
 36                                  0
 37                            END
 38                         ELSE
 39                            12 / 24
 40                      END,
 41                    'hh24:mi:ss'),
 42              'dd.mm.yyyy hh24:mi:ss');

MOD_DATE            AM_PM      RESULT
------------------- ---------- -------------------
31.05.1998 00:00:00 2:34:00PM  31.05.1998 14:34:00
05.06.2003 00:00:00 7:25AM     05.06.2003 07:25:00
13.06.2003 00:00:00 12:00AM    13.06.2003 00:00:00
26.06.2013 00:00:00 12:15AM    26.06.2013 00:15:00

SQL>
Re: date format [message #588548 is a reply to message #588547] Wed, 26 June 2013 07:51 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm getting a headache reading that, not that I can think of anything better.
Re: date format [message #588549 is a reply to message #588548] Wed, 26 June 2013 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I hope @prejib gets it too and realizes that one DATE datatype column solves most of his current and future problems.
Re: date format [message #588597 is a reply to message #588549] Wed, 26 June 2013 15:20 Go to previous messageGo to next message
Bill B
Messages: 1116
Registered: December 2004
Senior Member
Just a suggestion. If you want to store a truncated date and the time after midnight do it like the following

create table test(mod_date date, mod_time number(6));

insert into test values(trunc(sysdate),TO_CHAR(SYSDATE,'SSSSS'));

Then to reconstruct the time

select mod_date + (mod_time/86400) date_time from dual;
Re: date format [message #588637 is a reply to message #588547] Thu, 27 June 2013 04:23 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi
Still the query is not working properly . Can you please check the result
SQL> SELECT mod_date,
  2             am_pm,
  3             TO_DATE (
  4                TO_CHAR (mod_date, 'dd.mm.yyyy') || ' '
  5                || TO_CHAR (
                    TO_DATE (SUBSTR (am_pm, 1, LENGTH (am_pm) - 2), 'hh:mi:ss')
  6    7                      + CASE
  8                           WHEN SUBSTR (am_pm, -2) = 'AM'
  9                           THEN
 10                              CASE
 11                                 WHEN REGEXP_SUBSTR (am_pm, '\d+') = 12
 12                                 THEN
                                  -12 / 24
 13   14                                 ELSE
 15                                    0
 16                              END
 17                           ELSE
                            12 / 24
 18   19                        END,
 20                      'hh24:mi:ss'),
 21                'dd.mm.yyyy hh24:mi:ss')
 22                result
 23        FROM dat2
 24    ORDER BY result;

MOD_DATE               AM_PM      RESULT
---------------------- ---------- ----------------------
1998-05-31 12:00:00 AM 2:34PM     1998-05-31 02:34:00 PM
2003-06-05 12:00:00 AM 7:25PM     2003-06-05 07:25:00 PM
2003-06-13 12:00:00 AM 12PM       2003-06-13 12:00:00 AM
2003-06-13 12:00:00 AM 12AM       2003-06-13 12:00:00 AM
2003-06-13 12:00:00 AM 12:30AM    2003-06-13 12:30:00 AM
2003-06-13 12:00:00 AM 12:30PM    2003-06-13 12:30:00 AM
2003-06-13 12:00:00 AM 01AM       2003-06-13 01:00:00 AM
2003-06-13 12:00:00 AM 10AM       2003-06-13 10:00:00 AM
2003-06-13 12:00:00 AM 11:30AM    2003-06-13 11:30:00 AM
2013-06-30 12:00:00 AM 12:15PM    2013-06-30 12:15:00 AM

10 rows selected.

SQL>
Re: date format [message #588641 is a reply to message #588546] Thu, 27 June 2013 04:34 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
cookiemonster wrote on Wed, 26 June 2013 14:47

The correct solution here is to (...) get rid of the column entirely.

Otherwise, feel free to develop my (apparently) useless attempts. Include any CASE, DECODE or whatever you find appropriate to make it work. I don't feel like it any more.
Re: date format [message #589157 is a reply to message #588641] Tue, 02 July 2013 23:10 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi,
I have written one function to format the time in the column 'AM_PM' and return in the correct format . Now the same is working properly . My query is that,will there be any performance difference if we call function in SQL instead of writing these manipulations in query..
Also whether usage of function 'CONCAT' or '||'(Pipe) for combining string is better in terms of performance ?
SQL> create or replace FUNCTION test.time_format_fn( tme_passed IN VARCHAR2)
  2  RETURN VARCHAR2
  3  IS
  4    start_str VARCHAR2(30);
  5    end_str   VARCHAR2(30);
  6    tme       VARCHAR2(30);
  7  BEGIN
  8      tme := Upper(Replace(tme_passed, ' ', NULL));
  9
 10      IF ( tme LIKE '%:%' ) THEN
 11        start_str := Upper(Substr(tme, 0, Instr(tme, ':') - 1));
 12
 13        end_str := Upper(Substr(tme, Instr(tme, ':'), Length(tme)));
 14
 15        IF ( Length(start_str) = 1 ) THEN
 16          start_str := '0'||start_str||end_str;
 17        ELSE
 18          start_str := start_str ||end_str;
 19        END IF;
 20      ELSE
 21        start_str := Upper(Substr(tme, 0, Length(tme) - 2));
 22
 23        end_str := Upper(Substr(tme, Length(tme) - 1, Length(tme)));
 24
 25        IF ( Length(start_str) = 1 ) THEN
 26          start_str := '0'
 27                       ||start_str
 28                       ||':00'
 29                       ||end_str;
 30        ELSE
 31          start_str := start_str
 32                       ||':00'
 33                       ||end_str;
 34        END IF;
 35      END IF;
 36
 37      --dbms_output.put_line('cONVERTED '||START_STR);
 38      RETURN start_str;
 39  END time_format_fn;
 40
 41  /

Function created.

SQL> SELECT mod_date,am_pm,
        dat2.mod_date
        ||' '
        ||time_format_fn(dat2.am_pm)
result
FROM   test.dat2
ORDER  BY result;  

MOD_DATE  AM_PM      RESULT
--------- ---------- ----------------------------------------
05-JUN-03 7:25PM     05-JUN-03 07:25PM
13-JUN-03 01AM       13-JUN-03 01:00AM
13-JUN-03 10AM       13-JUN-03 10:00AM
13-JUN-03 11:30AM    13-JUN-03 11:30AM
13-JUN-03 12AM       13-JUN-03 12:00AM
13-JUN-03 12PM       13-JUN-03 12:00PM
13-JUN-03 12:30AM    13-JUN-03 12:30AM
13-JUN-03 12:30PM    13-JUN-03 12:30PM
30-JUN-13 12:15PM    30-JUN-13 12:15PM
31-MAY-98 2:34PM     31-MAY-98 02:34PM

10 rows selected.



Re: date format [message #589158 is a reply to message #589157] Tue, 02 July 2013 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
GIGO!
http://en.wikipedia.org/wiki/GIGO

strings are neither date nor time
If you actually & correctly used DATE datatype, you would not have to re-invent the existing TO_CHAR() function.

[Updated on: Tue, 02 July 2013 23:38]

Report message to a moderator

Re: date format [message #589160 is a reply to message #589158] Wed, 03 July 2013 00:16 Go to previous messageGo to next message
Littlefoot
Messages: 19687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
prejib
whether usage of function 'CONCAT' or '||'(Pipe) for combining string is better in terms of performance ?

No difference. However, CONCAT accepts only 2 parameters (i.e. you can concatenate only 2 strings together), while double pipe operator accepts many of them. There are some limitations as well (such as result's max length and stuff), but that's all described in documentation so - read it, if you are interested in more details.
Re: date format [message #589182 is a reply to message #589160] Wed, 03 July 2013 03:51 Go to previous message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Thanks to everyone...
Previous Topic: While loop
Next Topic: Creating Composite Unique Key with only 1 column allowed to be changed.
Goto Forum:
  


Current Time: Tue Oct 21 23:56:32 CDT 2014

Total time taken to generate the page: 0.09076 seconds