[b]Extracting date/time from number of seconds[/b] 1

[madsstiig]

Hi.
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?
I asked in an SQL forum 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
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'.

 

[lbass]

If the field is a number, it only has separators because it is formatted that way, so try:

dateadd("s",{MAP_JOB.DATE_TIME_CREATED}, date(1970,1,1))

This returns 9/10/2010 07:16:47 AM, off by two hours--not sure why. Was your example correct? Note the 1970, too.

-LB

 

[madsstiig]

Fantastic! That did it.
Can I do anything about the 2 hours 'delay'?

 

[madsstiig]

Yes I could:
dateadd("s",{MAP_JOB.DATE_TIME_CREATED}, datetime(1970,1,1,02,00,00))