January 18, 2013 at 9:43 am
Using SSIS I have imported the Cisco CDR (Call Detail Reporting) flat file into MS SQL 2008. The dates in the file are now in a numeric field. Cisco stores the dates as a number and a calculation is needed to change this to a readable date. I have found examples on how to convert this using Excel to a readable date, but cannot seem to find a way to do this using SQL so I can have a good date show up on the reports. I gather this is called a Unix timestamp based on seconds since standard epoch 1/1/1970.
The calculation to do this in Excel is
(C5-18000)/86400+DATE(1970,1,1) where C5 is the value to be converted
So when the number is 1353098756 the date is 11/16/12 15:45 (when formatted correctly in Excel). How would I write the select statement in SQL to make this conversion. Since I always want this to be a date, I could also (this is even further out of my league) convert it in the SSIS import portion, which is another subject.
Thanks for any help
January 18, 2013 at 10:20 am
I found that this code will translate this corretly is DATEADD(s,cdr.[dateTimeOrigination],'19700101'). Now trying to addthis to the report but not sure if this goes into the Dataset Properties or into the report column. So much closer than before.
January 18, 2013 at 11:14 am
And now to find out that DatAdd in SSRS is different then the DateAdd command when writing a query is SQL. This does not make sense on how to do this conversion in SSRS. Any ideas how to add the EPOCH dat ein SSRS?
January 18, 2013 at 1:45 pm
Thanks to all who have looked at this and thought about it. I found the answer buried on a post somewhere. Through all the confusing ones out there where the format may change and such, the truth ended up being that the format was the same but the Epoch date had to be surrunded by double quotes and not single quotes. The end idea was
=DateAdd("s", Fields!dateTimeConnect.Value,"1970-01-01 00:00:00")
The report runs perfectly. Thanks all!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply