Extracting date/time from number of seconds

[madsstiig]

I'm working on a report in Crystal Report XI that uses a DB (Oracle 10g based) with a time field (called {MAP_JOB.DATE_TIME_CREATED}) that is actually the number of seconds past january 1st 1972 i think.
How do i extract that into an understandable date/time format?
Best regards Mads Stiig Nielsen, Denmark.

 

[Thargy]

madsstiig,

I suggest that you create a function to do this for you, as it appears to be something that you'll be needing regularly. It is notoriously difficult to get such things correct, as you have to take into account leap days and leap years (e.g. February 29th 2000).

if the function was called MADSTIG, You could then do something like:-

SELECT MADSTIG(DATE_TIME_CREATED) FROM SOME_TABLE_OR_OTHER;

If you would like some assistance with the function itself, then you will have to provide us with explicit details of the table and fields involved and sample data with expected outcomes.

Regards

T

 

[LKBrwnDBA]

Post some example data and expected result...


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

Lk,

hope springs eternal, and I strive to live in it.

Regards

T

 

[Dagon]

All you would need is to convert it to a date using:

to_date('01-jan-1972', 'dd-mon-yyyy') +
<number of seconds>/(24*60*60)

Once it's converted to proper date, you can then just apply the usual TO_CHAR functions to format it however you want.

For Oracle-related work, contact me through Linked-In.

 

[Thargy]

Doh!

face palm.

<slinks off in acute embarassment>

Regards

T

 

[madsstiig]

Hi.
I have an example.
The field {MAP_JOB.DATE_TIME_CREATED}=1.284.103.007,00
is actually 2010-09-10, 09:16:47 (a.m.)
Is this info any useful?

 

[Dagon]

I'm not very familiar with your numeric notation, but assuming that "," is a decimal point, I would make the starting point '01-jan-1970 02:00:00':

SQL> select to_char(to_date('2010-09-10, 09:16:47', 'YYYY-MM-DD, HH24:MI:SS') - (1284103007/(24*60*60)), 'YYYY-MM-DD, HH24:MI:SS') as start_date from dual

START_DATE          
--------------------
1970-01-01, 02:00:00
1 row selected.

For Oracle-related work, contact me through Linked-In.