julian date conversion utility

Answered Question
Jul 10th, 2008
User Badges:

Hi cisco used to provide an unsupported utility with call manager to convert date formats in data dump files from sql query's. Can anybody provide me the link for that? I can't find it anywhere and all of the web based converters always have wrong dates.

Correct Answer by Rob Huffman about 9 years 2 weeks ago

Hi Ross,


Here is some related info (I don't remember ever seeing an actual Utility);


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


  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (7 ratings)
Loading.
Correct Answer
Rob Huffman Thu, 07/10/2008 - 09:37
User Badges:
  • Super Red, 40000 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

Hi Ross,


Here is some related info (I don't remember ever seeing an actual Utility);


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


rossporubski Thu, 07/10/2008 - 10:14
User Badges:

That's the one. basically I need to convert dates in order to run a specific cdr and for some reason the online converters are always all out of whack. Thanks for your help!

Rob Huffman Thu, 07/10/2008 - 12:15
User Badges:
  • Super Red, 40000 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

Hi Ross,


Good stuff! You are always welcome my friend.


Take care,

Rob

noeparenteau Tue, 09/09/2008 - 13:07
User Badges:

Rob, is there a reason why when I change the excel calc to "=A1/86401.27415+DATE(1970,1,1)" that the correct date/time appears?


I tried the calc of "=A1/86400+DATE(1970,1,1)", but the time was "ahead" by 5 hours???




krishnamurthy.mh Fri, 09/26/2008 - 03:36
User Badges:

Hi Rob,

We are facing similar type of problem.

Currently running UCM 6.1.2.

Third party call billing software is used.

In the billing report it showing year as 1970...


Plds help me out.


Regards,

Rob Huffman Fri, 09/26/2008 - 05:51
User Badges:
  • Super Red, 40000 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

Hey Krishna/Noe,


First off, Noe, sorry for missing your post (my bad!) Have a look at this thread that explains the need to manipulate the formula depanding on what time zone you reside in;


http://forum.cisco.com/eforum/servlet/NetProf?page=netprof&forum=Unified%20Communications%20and%20Video&topic=IP%20Telephony&topicID=.ee6c829&fromOutline=&CommCmd=MB%3Fcmd%3Ddisplay_location%26location%3D.2cc0c979


Krishna, you will need to engage the developers of the 3rd party billing software to add the proper conversion that will add the correction from UTC Time as this is the way that the CDR's will always be presented by CCM.


Hope this helps!

Rob

noeparenteau Fri, 09/26/2008 - 11:29
User Badges:

Rob, Thanks for you time and effort on my questions...


after reading the articles you listed, I have finnaly found a excel formula that works (central time) for changing my CDR date/time fields into valid timestamps.


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


Make sense?


Noe


Rob Huffman Fri, 09/26/2008 - 17:03
User Badges:
  • Super Red, 40000 points or more
  • Hall of Fame,

    Founding Member

  • Cisco Designated VIP,

    2017 IP Telephony, Unified Communications

Hi Noe,


Great work my friend! Makes perfect sense.


Have a great weekend :)

Rob

Actions

This Discussion