Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

Bronze

CDR-SQL Consult

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

1 ACCEPTED SOLUTION

Accepted Solutions
Hall of Fame Super Red

Re: CDR-SQL Consult

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.....

5 REPLIES
Hall of Fame Super Red

Re: CDR-SQL Consult

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

Bronze

Re: CDR-SQL Consult

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

Hall of Fame Super Red

Re: CDR-SQL Consult

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.....

Bronze

Re: CDR-SQL Consult

Thanks a lot Rob for your help and suggestions.

Best regards

David

Hall of Fame Super Red

Re: CDR-SQL Consult

Hi David,

You are most welcome my friend!

Take care,

Rob

123
Views
0
Helpful
5
Replies
CreatePlease login to create content