Convert Varchar to Datetime data type

[nread]

Hi everyone

I'm not much of an Oracle person however i would appreciate it if someone could help me out here...

I have a table with various fields which are varchar's and i need to convert them to a datetime format... This is the format :

2004:05:10:15:39:43.477

Hope someone can help....

Thanks
N

 

[jaggiebunnet]

try

select to_date(substr('2004:05:10:15:39:43.477', 1, length('2004:05:10:15:39:43.477') - 4), 'RRRR:MM:DD:HH24:MI:SS') 
from dual

The reason for the substr is that you can't store milliseconds in version 8. In version 9 you have a Timestamp type which would do that job.

 

[nread]

That works fine...! Thanks very much.

N

 

[nread]

Actually

Just looking at the results it appears that only the date is being pulled back and that the time part is being ignored...?

Any ideas?

Thanks
N

 

[sem]

That's just an illusion In fact this is a formatting issue. You may specify format mask either on session or statement level

alter session set NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS'

or

select TO_CHAR(<FIELD>,'YYYY:MM:DD:HH24:MI:SS') from <TABLE>

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

NRead,

I infer from your input format that what you want is more granular/precise than the format that appears in JaggieBunnet's post that uses the "TO_DATE..." function. Not to detract from other posts, I believe what you want, instead of Oracle's DATE column type, is Oracle's TIMESTAMP column type.

Section 1 -- Creating a TIMESTAMP data-type column:

create table times (b timestamp(4));

Section 2 -- Populating the column from system date/time:

insert into times values(systimestamp);

Section 3 -- Populating the column from a literal or from some other character-style string:

insert into times values
	  (to_timestamp('2004:05:10:15:39:43.477','yyyy:mm:dd:hh24:mi:ss.ff3'));

Section 4 -- Displaying a timestamp column:

select * from times;

B
--------------------------
08-JUL-04 10.08.58.9800 AM
13-DEC-03 12.56.44.0000 AM

2 rows selected.

Notice in the above select of a TIMESTAMP column, we do not need Oracle's "TO_CHAR..." function to display otherwise-hidden precisions of time.

Let me know if this alternative is closer to what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:12 (08Jul04) UTC (aka "GMT" and "Zulu"), 10:12 (08Jul04) Mountain Time)

 

[jaggiebunnet]

As I mentioned above, Timestamp is not available in oracle 8 and I am nearly certain it is not available in 8i either (don't have a version handy to confirm that).

 

[SantaMufasa]

Jaggiebunnet,

I deserve a "time-out" for that one. Sorry I didn't read close enough and for forgetting which forum I was in. My bad...please forgive.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:54 (08Jul04) UTC (aka "GMT" and "Zulu"), 12:54 (08Jul04) Mountain Time)