convert number to date

I have a Table that represent many istant time with two column:
Column 1:
istant time that is writted by a number 10 characters that are the seconds from 1/1/2000;

Column 2:
also in this column there is a data, that is year (YYYY): months (MM) ; Day (DD).

I need to rapresent the visible colum outside converting the first colum, or if is useful, also the second column, with the format:

year (YYYY): months (MM) ; Day (DD) : hour (HH) minute (MM) second (SS).

Thanks for help

re: convert number to date

Hi Lucaluca,

Please post your table structure and sample data with expected output.

re: convert number to date

his is the format (it is a "vista" from a table)


COLUMN DATA TYPE
DATA_EVENTO DATE
SECONDO_EVENTO NUMBER(10,0)

Comment for the "DATA EVENTO": it is the correct day of the timestamp
Comment of the "SECONDO EVENTO": it is the exact second day of the
timestamp, but from 1/1/2000)

Conversion that I need: new value (maybe: new column value, example: "ISTANTE TEMPORALE" that rapresent the timestamp
in a simple format to be read.
Example should be: hh:mm:ss , because the date is still knew, or DATE:HH:MM:SS, if is is more simple to generate

So is necessary to use some function to convert seconds to date and hh.mm.ss, starting from a zero point that is 1/1/2000

Best will be this function writable in a normal matemathic formula in ORACLE, because I use a vista in a Oracle 10, and I'm not very practice

Thanks

re: convert number to date

Please post the sample data of both the columns and the expected output format of the sample data

re: convert number to date

data types
DATA_EVENTO DATE
SECONDO_EVENTO NUMBER(10,0)

example actual output format (for example, in csv format)
;"01-NOV-09";"310430379";

example output format that I try to create (for example, in csv format)
;"01-NOV-09";"15:01:27";

that is, in other way: "01-NOV-09, 15:01:27" ore something like that

re: convert number to date

To convert the first column, try using:

re: convert number to date

thank you for the answer
my problem actually is that the type date is on the first column, the type number is on the second column, and represent seconds from 1/1/2000, that I need to convert in a normal date, hours:minuts:seconds format.

re: convert number to date

try this query: