How to parse date/time recorded by CDR

[lopezasp]

Hi,

I want to make my own reporting tool instead of using Cisco ART. But my problem is, I dont know how to parse or to convert the date/time format on the CDR. Is there anyone who can help with this problem?

Thanks

[Tom Dillon]

Here's the info from Cisco document:

http://www.cisco.com/warp/public/788/AVVID/ts_ccm_301_sec7.htm

Time Values

All time values are represented as unsigned 32 bit integers. This unsigned integer value is displayed from the database as a signed integer.

This field is a time_t value that is obtained from the Windows NT (2000) system routines. The value is a coordinated universal time (UTC) value and represents the number of seconds since Midnight (00:00:00) Jan. 1, 1970.

Deciphering the Time Stamp

Using Microsoft Excel, you can write a formula to make converting this time stamp a little easier. If the value is in cell A1, you can make another cell:

=A1/86400+DATE(1970,1,1)

There are 86400 seconds in a day.

Then, format the resulting cell as a date/time field in Excel.

[cliff]

The date time format in the CDR within the database table is a number that equals the number of seconds that have passed since 1/1/1970 12:00:00 am. You can convert from the value they have in the table to a useable date time value by using the DateAdd function in T-SQL.

I've been doing some of the same thing you are doing. In my case, I wanted the date times in local time (which is GMT - 7 hours). My function to convert this looks like this:

dateadd(hh,-7, dateadd(s, datetimeorigination, '1/1/1970 00:00:00.000'))

Notice that there are two dateadds in there. The first converts from Cisco's stored value to a human readable GMT date/time value. The outer one converts it to MDST.

Hope this helps.