convert number to date

[lucaluca]

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

[OraMaster]

Hi Lucaluca,

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

[lucaluca]

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

[amitpatel66]

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

[lucaluca]

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

[amitpatel66]

To convert the first column, try using:

[code=oracle]

SELECT TO_CHAR(DATA_EV ENTO,'DD-MON-YY HH:MI:SS') FROM table_name;

[/code]

[lucaluca]

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:se conds format.

[amitpatel66]

try this query:

[code=oracle]
select trunc((31043037 9/60)/60) ||
to_char(trunc(m od(310430379,36 00)/60),'09') ||
to_char(mod(mod (310430379,3600 ),60),'09')
from dual;
[/code]