Home » SQL & PL/SQL » SQL & PL/SQL » Date Format
Date Format [message #611535] |
Thu, 03 April 2014 08:00 |
|
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 #611544 is a reply to message #611540] |
Thu, 03 April 2014 09:13 |
|
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 |
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 |
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 |
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 |
|
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 |
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 #611556 is a reply to message #611554] |
Thu, 03 April 2014 10:25 |
|
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.
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 |
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 14:38:53 CDT 2024
|