CDR-SQL Consult

Answered Question
Nov 28th, 2007
User Badges:
  • Bronze, 100 points or more

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

David

Correct Answer by Rob Huffman about 9 years 5 months 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)


http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a008056bd4e.shtml



Hope this helps! Let us know.

Rob



Please remember to rate helpful posts.....

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Rob Huffman Wed, 11/28/2007 - 06:40
User Badges:
  • Super Blue, 32500 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

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


http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801d71c5.shtml#understand



Hope this helps!

Rob


PS: I think these tools work as well :)


http://www.iwebtool.com/unix_time_converter

david-lima Tue, 12/04/2007 - 06:06
User Badges:
  • Bronze, 100 points or more

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?

Thanks

David

Correct Answer
Rob Huffman Tue, 12/04/2007 - 06:31
User Badges:
  • Super Blue, 32500 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

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)


http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a008056bd4e.shtml



Hope this helps! Let us know.

Rob



Please remember to rate helpful posts.....

david-lima Fri, 12/28/2007 - 05:14
User Badges:
  • Bronze, 100 points or more

Thanks a lot Rob for your help and suggestions.

Best regards

David

Rob Huffman Fri, 12/28/2007 - 07:02
User Badges:
  • Super Blue, 32500 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

Hi David,


You are most welcome my friend!


Take care,

Rob

Actions

This Discussion