Covert Numeric field into Time? 1

[calandrelli]

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?

 

[TISIBS]

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

 

[TISIBS]

'trunc' in my last posting is obsolete ...

 

[calandrelli]

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.

 

[carp]

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?

 

[SantaMufasa]

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:

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.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

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.

 

[calandrelli]

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.

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...