Money data type to store date values in SQL Server?

Question

I am looking into some performance issues on a database that I have just inherited. I can see that there are a lot of implicit conversions because of queries casting money data type into date. It looks like almost all the date fields are having money data type.

Can someone tell me a good and valid reason as to why someone would use money data type for dates?

Thanks!

Answer 1

There is absolutely no good and valid reason for this practice.

Both datetime and money are 8 bytes. datetime has 4 bytes reserved for the date and 4 for the time.

money has a range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. When casting from money to datetime the integer portion is treated as the number of days since 1900-01-01 and the fractional portion as the percent of the day.

This means that you lose precision in the time part (each 0.0001 increment = 8.640 seconds) and gain useless extra scale in the date part by being able to store numbers far too large to ever cast successfully to datetime (which has a maximum bound of 9999 AD)

If possible fix the schema.