Home » SQL & PL/SQL » SQL & PL/SQL » Date Format
Date Format [message #611535] Thu, 03 April 2014 08:00 Go to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Hi,
I have this requirement of changing date fields as shown in below examples:

01:29 AM -> 129A
12:32 PM -> 1232P
09:30 AM -> 930A
09:00 AM -> 9A

I am using below query which help me in most cases but not in last one where 09:00 AM is converting to 900A.

select RTRIM(LTRIM(TO_CHAR (
               (FROM_TZ (CAST (sysdate AS TIMESTAMP), '+00:00') AT TIME ZONE 'America/New_York'),
               'HHMIAM'), 0), 'M') 
  from dual;


Thanks in advance.
Re: Date Format [message #611536 is a reply to message #611535] Thu, 03 April 2014 08:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to drop minutes entirely when they're 00 the simplest solution would be a case statement.
Re: Date Format [message #611537 is a reply to message #611535] Thu, 03 April 2014 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the type of the input data?
Why to you use FROM_TZ and AT TIME ZONE?

Re: Date Format [message #611539 is a reply to message #611537] Thu, 03 April 2014 08:18 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Hi Michel,

Input data type is DATE.

FROM_TZ was used because this was user defined query and they just need the output as i suggested. However, we can drop this if not necessary.

Thanks
Re: Date Format [message #611540 is a reply to message #611535] Thu, 03 April 2014 08:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Based on examples you posted:

09:00 AM -> 9A

And

00:09 AM -> 9A

So how will you distinguish the two?

SY.
Re: Date Format [message #611544 is a reply to message #611540] Thu, 03 April 2014 09:13 Go to previous messageGo to next message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Hi Solomon,

The input data using in query is of DATE type and will be in format like '1998/05/31:12:00:00 AM'.

So i think when we have 00:09 AM then it will be used as 12:09 AM and hence will be shown as 1209A.

Please see below attempts of my testing:


SQL> SELECT   TO_CHAR (TO_DATE ('1998/05/31:12:09:00 AM', 'yyyy/mm/dd:hh:mi:ss AM'),
  2                    'HH:MI AM')
  3              HH_AM_Format
  4    FROM   DUAL;

HH_AM_FORMAT
---------------------------------------------------------------------------
12:09 AM

SQL> 


SQL> SELECT   TO_CHAR (TO_DATE ('1998/05/31:09:00:00 AM', 'yyyy/mm/dd:hh:mi:ss AM'),
  2                    'HH:MI AM')
  3              HH_AM_Format
  4    FROM   DUAL;

HH_AM_FORMAT
---------------------------------------------------------------------------
09:00 AM

SQL> 


SQL> 
SQL> SELECT   TO_CHAR (TO_DATE ('1998/05/31:00:09:00 AM', 'yyyy/mm/dd:hh:mi:ss AM'),
  2                    'HH:MI AM')
  3              HH_AM_Format
  4    FROM   DUAL ;
SELECT   TO_CHAR (TO_DATE ('1998/05/31:00:09:00 AM', 'yyyy/mm/dd:hh:mi:ss AM'),
                            *
ERROR at line 1:
ORA-01849: hour must be between 1 and 12


SQL> 


Please let me know if I missed something.


Re: Date Format [message #611547 is a reply to message #611544] Thu, 03 April 2014 09:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Yes, I was wrong:

SYS@orcl12 > SELECT TO_CHAR(TO_DATE('1998/05/31:00:09:00 AM','yyyy/mm/dd:hh:mi:ss AM'),'HH:MI AM') HH_AM_Format FROM DUAL;
SELECT TO_CHAR(TO_DATE('1998/05/31:00:09:00 AM','yyyy/mm/dd:hh:mi:ss AM'),'HH:MI AM') HH_AM_Format FROM DUAL
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12


SYS@orcl12 > SELECT TO_CHAR(TO_DATE('1998/05/31:12:09:00 AM','yyyy/mm/dd:hh:mi:ss AM'),'HH:MI AM') HH_AM_Format FROM DUAL;

HH_AM_FO
--------
12:09 AM

1 row selected.

SYS@orcl12 >


SY.

[Updated on: Thu, 03 April 2014 09:36]

Report message to a moderator

Re: Date Format [message #611549 is a reply to message #611547] Thu, 03 April 2014 09:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
with t as (
           select to_date('01:29 AM','hh:mi am') dt from dual union all
           select to_date('12:32 PM','hh:mi am') from dual union all
           select to_date('09:30 AM','hh:mi am') from dual union all
           select to_date('09:00 AM','hh:mi am') from dual
          )
select  ltrim(regexp_replace(to_char(dt,'HHMIAM'),'(00)?(.)M$','\2'),'0') x
  from  t
/

X
------------------
129A
1232P
930A
9A

4 rows selected.

SCOTT@pdb1orcl12 > 


SY.

[Updated on: Thu, 03 April 2014 10:00]

Report message to a moderator

Re: Date Format [message #611550 is a reply to message #611544] Thu, 03 April 2014 10:10 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
I am afraid, that, even in this case, some people will really dislike you for implementing this "requirement".
However, here we go:
SQL> with x as ( select trunc(sysdate) + 660/1440 d from dual
  2    union all select trunc(sysdate) + 70/1440 from dual
  3    union all select trunc(sysdate) + 710/1440 from dual
  4    union all select trunc(sysdate) + 665/1440 from dual
  5    union all select trunc(sysdate) + 75/1440 from dual
  6    union all select trunc(sysdate) + 540/1440 from dual )
  7  select to_char( d, 'dd.mm.yyyy hh:mi:ss AM' ) input_date
  8       , translate( to_char( d, 'hhmiAM' ), 'A0M', 'A' ) output_mess
  9  from x;

INPUT_DATE             OUTPUT
---------------------- ------
03.04.2014 11:00:00 AM 11A
03.04.2014 01:10:00 AM 11A
03.04.2014 11:50:00 AM 115A
03.04.2014 11:05:00 AM 115A
03.04.2014 01:15:00 AM 115A
03.04.2014 09:00:00 AM 9A

(not sure whether zeroes shall be removed from the middle of the string - see the fourth row)
Re: Date Format [message #611551 is a reply to message #611549] Thu, 03 April 2014 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or without regexp:
SQL> with t as (
  2             select to_date('01:29 AM','hh:mi am') dt from dual union all
  3             select to_date('12:32 PM','hh:mi am') from dual union all
  4             select to_date('09:30 AM','hh:mi am') from dual union all
  5             select to_date('09:00 AM','hh:mi am') from dual
  6            )
  7  select trim('0' from to_char(dt,'HHMI'))||substr(to_char(dt,'AM'),1,1) x from t
  8  /
X
-----
129A
1232P
93A
9A

Re: Date Format [message #611552 is a reply to message #611549] Thu, 03 April 2014 10:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And without ltrim:

with t as (
           select to_date('01:29 AM','hh:mi am') dt from dual union all
           select to_date('12:32 PM','hh:mi am') from dual union all
           select to_date('09:30 AM','hh:mi am') from dual union all
           select to_date('09:00 AM','hh:mi am') from dual
          )
select  regexp_replace(to_char(dt,'HH:MIAM'),'^0?(\d\d?):(00)?(\d\d?)?(A|P)M$','\1\3\4') x
  from  t

X
------------------
129A
1232P
930A
9A

4 rows selected.

SCOTT@pdb1orcl12 >


SY.

[Updated on: Thu, 03 April 2014 10:19]

Report message to a moderator

Re: Date Format [message #611553 is a reply to message #611550] Thu, 03 April 2014 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
03.04.2014 11:05:00 AM 115A


Should not this give 1105A?
But I admit I don't completely understand OP's requirement.

Re: Date Format [message #611554 is a reply to message #611551] Thu, 03 April 2014 10:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 03 April 2014 11:16

Or without regexp:


9:30AM ended up as 93A

SY.
Re: Date Format [message #611556 is a reply to message #611554] Thu, 03 April 2014 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, don't completely understand OP's requirement. Wink
Maybe this (mimicking your output):
SQL> with t as (
  2             select to_date('01:29 AM','hh:mi am') dt from dual union all
  3             select to_date('12:32 PM','hh:mi am') from dual union all
  4             select to_date('09:30 AM','hh:mi am') from dual union all
  5             select to_date('09:00 AM','hh:mi am') from dual
  6            )
  7  select ltrim(replace(to_char(dt,'HHMI'),'00'),'0')||substr(to_char(dt,'AM'),1,1) x from t
  8  /
X
-----
129A
1232P
930A
9A


[Updated on: Thu, 03 April 2014 10:26]

Report message to a moderator

Re: Date Format [message #611557 is a reply to message #611553] Thu, 03 April 2014 10:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Yes, as far as I understand 11:05:00 AM should be 1105A. I believe OP wants hours witous leading zeroes followed my two digit minutes except when minutes is 0, followed by A or P (AM/PM).

SY.
Re: Date Format [message #611558 is a reply to message #611556] Thu, 03 April 2014 10:28 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Thu, 03 April 2014 11:25

Maybe this (mimicking your output):


No, replacing 00 will screw up 10:0x AM/PM:


with t as (
           select to_date('10:09 AM','hh:mi am') dt from dual
          )
select ltrim(replace(to_char(dt,'HHMI'),'00'),'0')||substr(to_char(dt,'AM'),1,1) x

  from  t
/

X
--------------------
19A

1 row selected.

SCOTT@pdb1orcl12 > 


SY.
Re: Date Format [message #611582 is a reply to message #611558] Fri, 04 April 2014 00:26 Go to previous message
gauravgautam135
Messages: 33
Registered: December 2013
Member
Thanks All of you.

I have got my answer and using Solomon suggested regular expression with ltrim option.

Smile
Previous Topic: sysdate problem with between()
Next Topic: ORA-12899: value too large for column
Goto Forum:
  


Current Time: Thu Mar 28 14:38:53 CDT 2024