question

S_M avatar image
S_M asked

Any good reason to use a numeric (18,0) datatype for a date column in sql server and also some columns with an nvarchar datatype but storing numbers, decimals, negative digits, NULL & Zeroes in it???

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...

sql-server-2008-r2datedatatypesreporting_servicesweb-service
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.