Solved: julian date conversion utility

rossporubski · ‎07-10-2008

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.

Rob Huffman · ‎07-10-2008

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

View solution in original post

Rob Huffman · ‎07-10-2008

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 · ‎07-10-2008

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 · ‎07-10-2008

Hi Ross,

Good stuff! You are always welcome my friend.

Take care,

Rob

noeparenteau · ‎09-09-2008

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 · ‎09-26-2008

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 · ‎09-26-2008

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 · ‎09-26-2008

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 · ‎09-26-2008

Hi Noe,

Great work my friend! Makes perfect sense.

Have a great weekend :)

Rob

krishnamurthy.mh · ‎09-27-2008

Hi Rob,

Thanks alot!!!

Happy weekend!!!

Thanks & Regards,

Krishna