OraFAQ Forum: SQL & PL/SQL » VARCHAR2 Convert to H:MI:SS tt (merged 2)

Home » SQL & PL/SQL » SQL & PL/SQL » VARCHAR2 Convert to H:MI:SS tt (merged 2) (Windows server 2008r2)

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

VARCHAR2 Convert to H:MI:SS tt (merged 2) [message #647019]

Mon, 18 January 2016 07:54

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

Hi Experts
Help required to convert Varchar2 to H:MI:SS tt

/forum/fa/12964/0/

Attachment: Editor sqls.png
(Size: 28.48KB, Downloaded 1279 times)

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647022 is a reply to message #647019]

Mon, 18 January 2016 08:14

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So it's a number of hours?
Convert it to a number. Add it to a date that has it's time set to midnight. use to_char on resulting date to get the time.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647023 is a reply to message #647022]

Mon, 18 January 2016 08:22

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

Senior Member
Account Moderator

Given the formula (X*24) I think it is a number of day.
So just add it to trunc(sysdate) and use to_char on the result.

@farrukh786
The expression "to_number(string*24)" is definitively wrong, it should be "to_number(string)*24".
And who has designed to store numbers in a VARCHAR2 column? It is one of the gates of hell.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647024 is a reply to message #647022]

Mon, 18 January 2016 08:27

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

Yes above image in right side the application front data entry in H:MI:SS AM the value is 4:30:00 PM

SELECT to_number ( to_date ( suoipi1.data_item*24, 'HH24MI'), 'H:MI:SS AM')

Finding error
ORA -01858: a non -numeric character was found where a numeric was excepted

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647025 is a reply to message #647024]

Mon, 18 January 2016 08:33

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

Thanks experts
i try to use sorry not working
SELECT to_char(to_date(( suoipi1.data_item)*24, 'HH24.MI'), 'HH:MI:SS AM')

[Updated on: Mon, 18 January 2016 08:41]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647027 is a reply to message #647025]

Mon, 18 January 2016 08:46

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That doesn't look like what either myself or Michel suggested.
First lets clarify - what exactly does the data in data_item represent?

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647028 is a reply to message #647027]

Mon, 18 January 2016 08:52

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

Data item represents below
Column Name ID Null? Data Type
DATA_ITEM 7 Y VARCHAR2 (120 Byte)

[Updated on: Mon, 18 January 2016 08:53]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647029 is a reply to message #647028]

Mon, 18 January 2016 08:54

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That's it's data type. It tells us nothing about what the data it contains is to be used for.
Is it number of days? hours? something else?

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647032 is a reply to message #647029]

Mon, 18 January 2016 10:32

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

This data_item contains numbers ,char, time storage
Every type of value, whether it is a string or a numeric value, is stored as a string.

/forum/fa/12965/0/

Attachment: storage.PNG
(Size: 49.78KB, Downloaded 1363 times)

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647033 is a reply to message #647032]

Mon, 18 January 2016 10:37

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

Senior Member
Account Moderator

How do you know if it is a time and what is the unit?

And who in the hell has designed such thing? he deserves a poke in the eye using a screwdriver.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647035 is a reply to message #647033]

Mon, 18 January 2016 10:50

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You're going to have serious problems converting 'In Service' to a time.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647036 is a reply to message #647033]

Mon, 18 January 2016 11:04

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

Since a week i am trying to take out the data from DATA_ITEM also succeeded but in the time h:mi:ss tt i stuck ,please find below the results.
This db designed by third party which we are using.

/forum/fa/12967/0/

Attachment: results.PNG
(Size: 21.94KB, Downloaded 1331 times)

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647037 is a reply to message #647036]

Mon, 18 January 2016 11:05

farrukh786
Messages: 7
Registered: January 2016
Location: iraq

Junior Member

I sql query is also attached.

Attachment: Sql query.txt
(Size: 5.96KB, Downloaded 1371 times)

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647039 is a reply to message #647037]

Mon, 18 January 2016 11:12

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

Senior Member
Account Moderator

1/ You still did not answer to our question: how do you know the unit? Is it day, hours, minutes, years?
2/ This is a very very bad query. Get rid of all subqueries in SELECT clause, there is a recent on this subject.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647043 is a reply to message #647039]

Mon, 18 January 2016 12:01

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

with sample as (
                select '16.5' min_ambient_time from dual
               )
select  to_char(to_date(min_ambient_time,'hh24.mi'),'hh:mi:ss pm') min_ambient_time
  from  sample
/

MIN_AMBIENT
-----------
04:05:00 pm

SQL>

SY.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647045 is a reply to message #647043]

Mon, 18 January 2016 12:04

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

Senior Member
Account Moderator

But 16.5 is not 16:05 but 16:30 (assuming this is a number of hours which we don't know).

[Updated on: Mon, 18 January 2016 12:06]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647054 is a reply to message #647045]

Mon, 18 January 2016 13:33

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

This is why this is a string, so dot isn't whole and fractional part separator. It is hours and minutes separator. So '16.5' is 16 hours 5 minutes and '16.50' is 16 hours 50 minutes. Bad design, OP should use DATE.

SY.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647055 is a reply to message #647054]

Mon, 18 January 2016 13:44

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

Senior Member
Account Moderator

So what 4447.111815 is?

And from the formula in the query I previously pointed: "to_number(string*24)",
it is a real number (of days converted to hours) not a string representing a time.

Quote:

Bad design, OP should use DATE.

OP uses the same column for many things, see its abstract above, not just times.
It is more than bad design, it is a crime. Smile

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647063 is a reply to message #647055]

Mon, 18 January 2016 14:09

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I've seen many apps (including Oracle owned) using relational database but having non-relational design where column VALUE meaning depends on column VALUE_TYPE value. E.g. VALUE = '16.5' means time if VALUE_TYPE='TIME' price if VALUE_TYPE = 'PRICE'.

SY.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647076 is a reply to message #647063]

Tue, 19 January 2016 02:26

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear,

Please Retrieve all the values using below query and present to the client,how to resolve this issue.


select  *
from    <Table Name>
where   regexp_like (data_item,'[0-9].[0-9]')

I hope,We can not interpret data as you defined in the table column.

[Updated on: Tue, 19 January 2016 02:27]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647077 is a reply to message #647076]

Tue, 19 January 2016 02:35

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

Senior Member
Account Moderator

Once again, you answer outside the actual question.
In addition, the answer is wrong.
So it will solve nothing.

[Updated on: Tue, 19 January 2016 02:35]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647078 is a reply to message #647077]

Tue, 19 January 2016 02:52

msol25
Messages: 396
Registered: June 2011

Senior Member

@Michel,

If data is wrong, then I asked to find out Numeric data Issue.

I hope this is not wrong.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647079 is a reply to message #647078]

Tue, 19 January 2016 02:54

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

Senior Member
Account Moderator

Your formula does not get a simple and single 0.
(But once more this is not the question.)

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647080 is a reply to message #647079]

Tue, 19 January 2016 02:57

msol25
Messages: 396
Registered: June 2011

Senior Member

Dear Michel,

Regarding single 0, We have to think in some other way.

But, Anyhow my concern is to find out data issue as per my given query.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647082 is a reply to message #647080]

Tue, 19 January 2016 03:00

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

Senior Member
Account Moderator

So your concern is to post a query that has nothing to do with the actual question.
You should write a blog for this.

[Updated on: Tue, 19 January 2016 03:02]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647083 is a reply to message #647082]

Tue, 19 January 2016 03:02

msol25
Messages: 396
Registered: June 2011

Senior Member

Michel Sir,

Solution should be the second priority.If data is not correct then how you can provide solution?

I hope there is not having any mistake from my end.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647084 is a reply to message #647083]

Tue, 19 January 2016 03:05

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

Senior Member
Account Moderator

Yes you're having mistake, read the question and read OP's query, you will see there is no problem (from OP's side) with the data.

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647085 is a reply to message #647084]

Tue, 19 January 2016 03:08

msol25
Messages: 396
Registered: June 2011

Senior Member

Quote:

How do you know if it is a time and what is the unit?

And who in the hell has designed such thing? he deserves a poke in the eye using a screwdriver.

Then,Why you have written like this in Above Post?

[Updated on: Tue, 19 January 2016 03:08]

Report message to a moderator

Re: VARCHAR2 Convert to H:MI:SS tt [message #647086 is a reply to message #647085]

Tue, 19 January 2016 03:11

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

Senior Member
Account Moderator

This was before OP posted his query (but this does not change my opinion on the design).
Once again, read it.

Report message to a moderator

Previous Topic:	CHAR '
Next Topic:	How do I find a value anywhere in a Oracle schema ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:07:49 CDT 2024