OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Data in AM/PM

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data in AM/PM [message #624437]

Mon, 22 September 2014 14:19

kr1347
Messages: 16
Registered: September 2014

Junior Member

Hi

Below is the code and output .. I want output to be displayed as 8:15 AM 5:00 PM

SELECT DISTINCT
FCLT_ID,
3 ,
min(trim(replace(LOWER(OFC_START_TM_TUE_TX),'abcdefghijklmnopqrstuvwxyz','')))||'AM' START_TIME,
MAX(trim(replace(LOWER(OFC_CLOSE_TM_TUE_TX),'abcdefghijklmnopqrstuvwxyz','')))||'PM' CLOSE_TIME
FROM NXPEPND1.TPN_STAGING_PRVDR_LOC
where OFC_START_TM_TUE_TX is not null and OFC_CLOSE_TM_TUE_TX is not null
and FCLT_ID = '2842130'
GROUP BY FCLT_ID

/forum/fa/12188/0/

[Updated on: Mon, 22 September 2014 14:43] by Moderator

Report message to a moderator

Re: Data in AM/PM [message #624438 is a reply to message #624437]

Mon, 22 September 2014 14:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what datatype are OFC_START_TM_TUE_TX & OFC_CLOSE_TM_TUE_TX

Report message to a moderator

Re: Data in AM/PM [message #624439 is a reply to message #624438]

Mon, 22 September 2014 14:30

kr1347
Messages: 16
Registered: September 2014

Junior Member

Its in varchar2

Report message to a moderator

Re: Data in AM/PM [message #624440 is a reply to message #624439]

Mon, 22 September 2014 14:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

so use standard string functions to format the data as you desire it.

BTW - it is a Bad Thing to store dates & times as VARCHAR2 or NUMBER. only DATE datatype should be used.

Report message to a moderator

Re: Data in AM/PM [message #624441 is a reply to message #624437]

Mon, 22 September 2014 14:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 18 September 2014 20:43

Michel Cadot wrote on Wed, 17 September 2014 17:18

...
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
...

[Updated on: Mon, 22 September 2014 14:43]

Report message to a moderator

Previous Topic:	Unable to drop a column in table.
Next Topic:	what is the issue in my query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:46:22 CDT 2024