understanding the datetimeconnect # in the SQL CDR's

[jkoniecki]

I am currently trying to export and make sence of the data in the SQL database when i look at the datetimeconnect field i recieve a 10 digit # is their some way of deciphering this #. This would really be helpful . Please let me know ..

jim koniecki

[jraarons]

If I recall right it is number of seconds since Jan 1, 1980. Another developer might be able to provide more details, etc.

If they were using Win32 time values;

The Win32 time format is used by the Win32 APIs. The date and time is stored in a 64-bit value that represents the number of 100 nanoseconds since January 1st, 1601. This date was chosen because it is 400 years prior to the start of a new century. At 100ns resolution 32 bits is good for about 429 seconds (or 7 minutes) and a 63-bit integer is good for about 29,247 years.

[dgoodwin]

Actually, it's the number of seconds since Midnight UTC time on January 1, 1970 (a.k.a. UNIX Epoch Time).

Anyway, if you just have one number you want to translate to a readable time, then use Excel.

Put the number in A1 and then in B1 type:

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

And then format the B1 cell to a date/time format that you can read. Keep in mind that the result will be in UTC (Greenwich) time zone format, so you can modify the formula to fix that if you like.

If you really want to make it easy on yourself then you can install ART.

[jkoniecki]

david.

thank you so much

life saver

jim k

[dgoodwin]

In case anyone cares, if you want the Excel formula to automatically adjust the time displayed to account for Eastern Time (-0500) then you can use this formula:

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

18000 = number of seconds in 5 hours

86400 = number of seconds in 1 day