Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Covert Numeric field into Time? 1

Status
Not open for further replies.

calandrelli

Technical User
Jun 14, 2002
69
US
I have a Date Field MM/DD/YYYY and a time value stored in a number field HH24MI that I want to convert to a single field but can't seem to figure it out. Just starting out simple I am trying to convert the number field to a time value.

Select
To_Date(JOB_ACTY_TM, 'HHMI') TIME_Field,
From IDEA.AMM_JOB_ACTY
Where JOB_ACTY_DT > to_date('12-10-2003','MM-DD-YYYY')

Any ideas?
 
You simply forgot '24'

Try the following:

SELECT to_date(to_char(trunc(SYSDATE), 'MM/DD/RRRR') || '2315','MM/DD/RRRRHH24MI')
FROM dual

In your case you have to replace SYSDATE with JOB_ACTY_DT and 2315 with JOB_ACTY_TM


Timo
 
Thanl you for your reply but it's not going to work for me. I just noticed that the time values do not meet the HH24MI format.

12:31pm is recorded as 1231

but

8:05am is recorded as 805

This makes ORACLE warn me that Hours must be between 0 and 23.
 
How is 8:05pm recorded?
If not 2015 and no AM/PM is included, how will you know which 8:05 you are looking at?
 
Calandrelli, if you have a column named JOB_ACTY_DT (which, by definition, also contains time down to the second), it should be a straightforward activity to consolidate all of your JOB_ACTY_TM values into JOB_ACTY_DT.

Second, if you choose to stay with your less-desirable "split"-expression design (DATE+varchar time), and presuming that you do store your times in 24-hour format (albeit 1 digit too short for values under "1000" (10 a.m.)), then you can resolve your problem in this fashion:
Code:
Select To_Date([b]substr('0'||JOB_ACTY_TM,-4)[/b], 'HHMI') TIME_Field, 
From IDEA.AMM_JOB_ACTY 
Where JOB_ACTY_DT > to_date('12-10-2003','MM-DD-YYYY')
Let us know if any of this helps resolve your need.


[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Mufasa, thank you for pointing me in the right direction. Here is my final query. The syntax may look a little starnge because I am actually working in a SQL Server 2000 database using a pass-through query to an Oracle Data Warehouse.
Code:
SELECT *
FROM
   OPENQUERY(addiw1, 'select To_Date((To_Char(JOB_ACTY_DT, ''MM-DD-YYYY'') 
   || (substr(''000''||JOB_ACTY_TM,-4))), ''MM-DD-YYYYHH24MI'')JOB_DT, 
   FROM IDEA.AMM_JOB_ACTY 
   WHERE 
   JOB_ACTY_DT > to_date(''12-10-2003'',''MM-DD-YYYY''))

I had to add '000' instead of '0' to handle times like 12:09am which were stored in the Oracle table as '9'. Looks like the datawarehouse is storing the serial time field as a numeric field.

Thanks Again...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top