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

Date & Time in SQL

Hi! is there a way to interpret the DateTimeConnect column in SQL ?? When we are generating CDRs, the value is something like that: 995295457.

I hope there is a way to do the conversion without using any other party application.

Thanks in advance,

Michel Nantel

Cisco Employee

Re: Date & Time in SQL

Yes, this value is the number of seconds since Midnight, January 1, 1970 expressed in UTC time. So a quick way to read this in a "normal" date/time format is to put the value in an Excel spreadsheet and write a formula like =(A1/86400) + DATE(1970,1,1) and then format the cell with the result to display the date and time. Note that this result will also be in UTC time so add/subtract as needed.

Community Member

Re: Date & Time in SQL

To get the correct EST, I use =((A1-14400)/86400) + DATE(1970,1,1)

Does anyone know the formula for converting the time in MS ACCESS, I tried the same formula as above, but it doesn't like the DATE(1970,1,1) part.

Community Member

Re: Date & Time in SQL

Thank you very much... but I have another question!!

I tried this in excel but when we want to diagnose our CDRs, we don't always have Excel on our servers... Is there a way to show the exact date & time in a SQL Query in the program SQL Query Analyzer program given with the SQL Server??

I tried the following command. The date works fine but the time always appears as 00:00:00 :

cast((DateTimeConnect/86400 + 25569) as smalldatetime)

[where 25569 is date(1970,1,1) in excel in text format)

DateTimeConnect newdatetime

--------------- ---------------------------

995910092 2001-07-25 00:00:00

995910167 2001-07-25 00:00:00

995910186 2001-07-25 00:00:00

Thank you in advance!

Michel Nantel

Cisco Employee

Re: Date & Time in SQL

I have not attempted this through a SQL query. You may want to consider installing ART, as it will take care of these conversions automatically for you. And the data can be presented in PDF format and can be downloaded or emailed. If you prefer to still have the data in SQL, the tool actually creates a database on the publisher called ART and the data can be found in the Tbl_Billing_Data table.

CreatePlease to create content