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!
谢谢!
2 个解决方案
#1
6
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.
datetime和money都是8字节。datetime预留了4个字节作为日期，4个字节作为时间。
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.
货币的取值范围从- 922337203,685,477.5808到922337,203,685,477.5807。当从货币转换到日期时间时，整数部分被视为1900-01-01之后的天数，小数部分被视为一天的百分比。
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)
这意味着您在时间部分失去了精度(每增加0.0001秒= 8.640秒)，而在日期部分增加了无用的额外比例，因为您能够存储太大的数字，以至于无法成功地转换到datetime(它的最大值为9999 AD)。
If possible fix the schema.
如果可能，修复模式。
#2
0
I think only for space-saving and to have sub-minute granularity, but design is obviously weird
我认为只有在节省空间的情况下才有细粒度，但是设计显然是很奇怪的
May be to avoid with this the DATETIME and SMALLDATETIME bounds, but if you say that it is often casting to date - it is not likely so.
可能是为了避免使用DATETIME和SMALLDATETIME边界，但是如果您说它经常被转换为date，那么不太可能是这样。
Update:
更新:
Since as @Martin Smith said - it is not saving space - then design is much more weird.
正如@Martin Smith所说——这并不能节省空间——那么设计就更奇怪了。
One more suggestion - the data was imported from old legacy DB system, where datetime values were stored in such a way.
还有一个建议——数据是从旧的遗留DB系统中导入的，其中以这种方式存储datetime值。
Obvoiusly - if you look for good and valid reason - there is none.
相反地，如果你寻找好的和有效的理由，就没有理由。
#1
6
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.
datetime和money都是8字节。datetime预留了4个字节作为日期，4个字节作为时间。
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.
货币的取值范围从- 922337203,685,477.5808到922337,203,685,477.5807。当从货币转换到日期时间时，整数部分被视为1900-01-01之后的天数，小数部分被视为一天的百分比。
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)
这意味着您在时间部分失去了精度(每增加0.0001秒= 8.640秒)，而在日期部分增加了无用的额外比例，因为您能够存储太大的数字，以至于无法成功地转换到datetime(它的最大值为9999 AD)。
If possible fix the schema.
如果可能，修复模式。
#2
0
I think only for space-saving and to have sub-minute granularity, but design is obviously weird
我认为只有在节省空间的情况下才有细粒度，但是设计显然是很奇怪的
May be to avoid with this the DATETIME and SMALLDATETIME bounds, but if you say that it is often casting to date - it is not likely so.
可能是为了避免使用DATETIME和SMALLDATETIME边界，但是如果您说它经常被转换为date，那么不太可能是这样。
Update:
更新:
Since as @Martin Smith said - it is not saving space - then design is much more weird.
正如@Martin Smith所说——这并不能节省空间——那么设计就更奇怪了。
One more suggestion - the data was imported from old legacy DB system, where datetime values were stored in such a way.
还有一个建议——数据是从旧的遗留DB系统中导入的，其中以这种方式存储datetime值。
Obvoiusly - if you look for good and valid reason - there is none.
相反地，如果你寻找好的和有效的理由，就没有理由。