Conversion from seconds to readable date 2

[frankrizzojerky]

Cisco Call Manager logs call data to sql and stores the dates in the format "seconds since midnight jan 1 1970.

My question is, how would I go about converting this to readable format either using a stored precedure or an sql query in access. I was able to sort of do something in access, but it doesnt work so well.

Here is the sql query that doesnt work so well:
SELECT dbo_CallDetailRecord.callingPartyNumber AS Caller, dbo_CallDetailRecord.finalCalledPartyNumber AS Called, dbo_CallDetailRecord.duration AS Duration, CVDate(Format(((dbo_dateTimeOrigination/86400)+DateDiff("d",#1/1/1900#,#1/1/1970#)),"dd mm yyyy hh:nn:ss&quot) AS dateTimeOrigination_dt
FROM dbo_CallDetailRecord
WHERE (((dbo_CallDetailRecord.callingPartyNumber)="1220&quot);


It doesnt really spit out the correct date conversions as far as I can tell, and it assigns the same date (I am assuming the date of the first call in the log) to all of the rows.

Any ideas?

 

[MaffewW]

oh yeah. not doing sums very well. I think then you want 14400 instead of 18000 (and if its not that I've gone an hour the wrong way so 21600 and if its not that I give up!)

Matt

Brighton, UK

 

[rathna01]

Frank,

Add 3600 to 18000 for Daylight Saving, dont substract 3600 from 18000, since you r on Eastern time. This Unix time converstion sucks...