I am looking for the SQL statement that can be used in a query (Query Analyzer) against the CDR database to convert the format from Epoch to MM/DD/YY 00:00:00.
I've googled "Unity Epoch time" (as mentioned on one forum posting) and even asked TAC, but no luck.
Any assistance would be greatly appreciated.
The statement you are looking for is:
DATEADD(s, dateTimeOrigination, '19700101')
i.e. select DATEADD(s, dateTimeOrigination, '19700101'), duration, callingPartyNumber, originalCalledPartyNumber from callDetailRecord
As a bonus, you can also use
CONVERT(varchar(6),duration/3600)+':'+RIGHT('0'+CONVERT(varchar(2),(duration % 3600)/60),2)+':'+RIGHT('0'+CONVERT(varchar(2),duration%60),2)
to convert the duration from seconds to H:M:S