Solved: SQL statement to convert Epoch time to Human Readable

dolah · ‎08-14-2008

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.

smallpygmy · ‎08-15-2008

Hi

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

View solution in original post

smallpygmy · ‎08-15-2008

Hi

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

dolah · ‎08-15-2008

Thank you, I'll try this today.

dolah · ‎08-29-2008

Thank you, this resolved my issue.

I used the DATEADD line and was able to print a readable report.

Debbie