Convert a Number Datatype to Date

[KWarren]

Hello,

I am a MS Sql Server guy, but have been given a project to tap into an Oracle Database. There is a particular column that is of Number Datatype representing when the record was written to the table.

The records in this column look like:
1,040,411,261,116
1,041,971,144,642
1,041,971,154,199
1,043,872,590,533
...
...
etc.

Does anyone know how to convert this in to a Date? For SQL server I have used something like:
select lastname, readerdescription, convert (datetime, (convert (float, hostdate)), 101)

to convert a really ugly Float datatype to a Date.

I'd like to know if I can do a convert or cast in Oracle of Number to Date. Any ideas?

thanks,

Kevin

[stecal]

You can treat numbers as characters and then use to_date to convert characters to date. The numbers/characters have to represent something that can be converted. What does "1,040,411,261,116" mean in terms of a date?

http://download-west.oracle.com/docs...ts4a.htm#34512
http://download-west.oracle.com/docs...7a.htm#1003595

[KWarren]

After some work with my calculator, I figured out that these numbers:

1,116,959,754,243
1,117,542,935,723
1,117,542,975,051
1,117,542,999,425
1,117,543,002,272
1,117,543,013,217
1,117,543,069,159
1,117,543,080,251
1,117,543,221,458
1,117,543,405,905
1,117,543,412,836

represent how many milliseconds have occured since 1/1 1970. So....in Oracle, would I do something like:

SELECT TO_DATE('createdatecolumn', 'Month DD, YYYY, HH:MI A.M.', 'NLS_Date_Language = American') from Table;

and then somehow use 1/1/1970 and milliseconds as a reference?