Hi All,
We have a user application which has been developed in USA by a service provider and it being used in UK.The application works fine for the users and the application managers including the DEV team in USA.
My question is that the application manager uses the front end to generate his reports (excel & UI) and other requirements, but he recently asked me to add some columns to one of his existing reports and asked me to add some date columns & date difference column.
Now i have recently joined this organisation as a DBA (SQL SERVER) (2008R2) and have some knowledge on reporting services. So when he expalined the requirement i have managed to gather most of the columns required for the report and created a stored procedure. But there are some columns in his requirement where dates are saved in a numeric datatype column and in another case where the column is an nvarchar but the data is saved as integers, nulls, negative digits and zeroes. The problem i am facing here is when trying to modify the procedure parameters on the date range as the data is stored as number, somewhat like these '438048000000, -214272000000,416880000000, 1132012800000, -636508800000' and this the DOB, date created, date updated columns.
I have found a way of converting the unix epoch date to yyyy-mm-dd format using the below dateadd funciton
dateadd(hour,0,(dateadd(second,(t1.date_created/1000),'1/1/1970')))as [Creation Date]
but still not managed to complete report due to the nvarchar columns where the data is being stored as numbers and finding it difficult to understand how to achieve the final report.
Any knowledge sharing, suggestions, corrections and help are more than appreciated.
Can provide more information if required.
Thanks in advance...
