在 SQL Server 中，将时间和日期拆分为两个单独的日期列是最佳实践吗？
将日期时间拆分为两个日期时间 SQL 列是最佳实践吗？
例如，2010-12-17 01:55:00.000 放入两列中，
- 其中一列包含日期时间 日期部分：2010-12-17 00:00:00.000
- 包含日期时间的一列 对于时间部分： 1900-01-01 01:55:00.000
我被告知这是最佳实践，因为在某些时候 SQL 2000 不允许在日期中输入时间？甚至有数据存储标准强制执行这一点，并且一些公司已确保其所有数据都以这种方式存储以符合某些数据存储标准？
如果是这样的话，我肯定有人听说过，这听起来很熟悉吗？
Is it best practice to split a dateTime in two datetime SQL columns?
For example, 2010-12-17 01:55:00.000 is put in two colums,
- one column containing a datetime for
the date portion: 2010-12-17 00:00:00.000 - one column containing a datetime
for the time portion: 1900-01-01 01:55:00.000
I'm being told this is best practice because at some point SQL 2000 didn't allow to put time in a date? and that there are even data storage standards that enforce this and that some companies have ensure that all their data is stored in that manner to comply to some data storage standards?
If this is the case, I'm sure someone heard about it here, any of this sounds familiar?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在 sql server 2008 中，您有日期和时间数据类型，因此这不再是问题。即使在 SQL Server 6 和 7 中，日期时间也始终允许时间，
人们将其分开的原因是因为对于 1 列中的所有内容，返回任意一天下午 3 点到 4 点之间下的所有订单的查询需要扫描，其中包含时间列可以通过查找来完成（快得多）
In sql server 2008 you have date and time data types so this becomes a non issue. datetime always allowed for time even back in sql server 6 and 7
the reason people split it up is because with everything in 1 column a query that returns all orders placed between 3 and 4 PM for any day requires a scan, with a time column this can be accomplished with a seek (much, much faster)
从 SQL 2005 开始，我只做一列。
如果您希望此信息可Sargable，我将使用 而是计算列。通过这种方式，您可以查询日期或时间或两者，并且您的应用程序代码仅负责维护一列。
Starting in SQL 2005 I would do only one column.
If you wanted this information to be Sargable I would use computed columns instead. This way you can query on date or time or both and your application code is only responsible for maintaining the one column.
我知道这已经很旧了，但您可能想要保持独立的另一个原因是用户输入（GenEric 在评论中说这是为了时间管理）。如果您允许用户将日期/时间作为单独的字段输入，并且您希望能够在任一字段为空的情况下保存数据，那么数据库中最好有 2 个单独的可为空字段。否则，我想您要么必须求助于某些日期值等于“空”的拼凑，要么添加额外的位字段作为“无时间/无日期”标志。
I know this is old, but another reason you might want to keep separate is for user input (and GenEric said in a comment that this is for time management). If you allow users to enter date/time as separate fields, and you want to be able to save the data with either field being empty, it is nice to have 2 separate null-able fields in your database. Otherwise I guess you either have to resort to kludges where certain date values equal "empty" or add extra bit fields as "no time / no date" flags.