Extracting date/time from number of seconds

[madsstiig]

I'm working on a report that uses a DB 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.

 

[ggriffit]

Mads,
I assume that you are looking to create a Livelink LiveReport / WebReport, sounds like this is a SQL question and the answer will depend on your database type - Oracle / SQL - so can you provide that ? There are SQL and DB Specific forums here as well which may be a better place to ask the question.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[madsstiig]

Hi Greg.

No I'm working on a CR report.
Teh database is an Oracle DB, version 10g.

 

[ggriffit]

Mads,
in which case I suggest you try the Crystal Reports (

http://www.tek-tips.com/forumsearch.cfm?SearchString=crystal+reports&type=Forum&sub=Go)

or Oracle group (

http://www.tek-tips.com/threadminder.cfm?pid=1177)

who would be better equipped to help with SQL queries.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[madsstiig]

Okay
I will try that.

 

[madsstiig]

Hi again.
I asked in one of the forums you suggested and got an answer.
Unfortunately I'm not too familiar with translating SQL statements to Crystal language.
Can someone plz help?
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

 

[madsstiig]

... oh and I have an example (which I think is used in the above solution...):
The field {MAP_JOB.DATE_TIME_CREATED}=1.284.103.007,00
is actually 2010-09-10, 09:16:47 (a.m.)
where . is the 'thousands separator'.

 

[ggriffit]

Mads,
Most of the people in this forum support an ECM product called Open Text Livelink / Content Server rather than Crystal Reports, so I suggest that you post the update in that forum -

http://www.tek-tips.com/forumsearch.cfm?SearchString=crystal+reports&type=Forum&sub=Go

- as you are more likely to get assistance there.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/