CDR-SQL Consult

Answered Question
Nov 28th, 2007

Hi friends, does somebody knows how to convert the values of the fields like DATETIMEORIGINATION or DATETIMECONNECT in date format in CDR database, using queries?

Thanks for your help

Best Regards


I have this problem too.
0 votes
Correct Answer by rob.huffman about 9 years 1 month ago

Hi David,

No worries my friend :) This doc actually is based on CCM 3.3 so you should be good to go. Have a look at Cause 1 and 2 instead of Cause 3 (which is specifically for CCM 4.1(3)

Hope this helps! Let us know.


Please remember to rate helpful posts.....

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
rob.huffman Wed, 11/28/2007 - 06:40

Hi David,

Hope all is well with you! I beleive this is referred to as "Universal Time". Here is some related info;

Date Format in the CDR Database

In order to select all calls after a certain date, you need to convert the date you want into a value in universal time and in seconds since January 1st, 1970.

For example, 973995954 translates to 11/12/00 2:25 AM. Complete these steps in order to decipher the time stamp.

Go to Microsoft Excel.

In cell A1 type the number that is found in the last record for dateTimeOrigination.

In cell A2, paste the formula =A1/86400+DATE(1970,1,1).

Right-click on cell A2 and select format cells.

Under the Number tab select Time where the format is 3/14/98 130 PM.

The result is the actual time in readable format.

From this excellent doc;

Using SQL Queries to Search the Call Detail Record with Cisco CallManager

Hope this helps!


PS: I think these tools work as well :)

david-lima Tue, 12/04/2007 - 06:06

Thanks Rob for you help. If isn't a problem, I have one more question about this matter, when I try to run CAR/ART reports, it show me: "30023:Data is not Available for the range selected", I read the Document ID: 68140 - DATA IS ABSENT IN REPORTS...- that explain this cause but for CCM 4.1(3) and later and I have CCM 3.3(4), I try to apply this solution but the COMMENT FIELD/CDR DB doesn't exist. Is there another procedure to fix this problem?



david-lima Fri, 12/28/2007 - 05:14

Thanks a lot Rob for your help and suggestions.

Best regards



This Discussion