我应该将日期分为日，月和年列吗？ [英] Should I separate a date into day, month, and year columns?

问题描述

我一直在尝试重新设计数据库中每个表的部分内容。我注意到许多不同的结构，包括只在一列中有完整的日期。但是，我一直在使用Day，Month和Year列，因此我可以轻松查找特定年份。制作日期栏比制作单独栏更有利吗？



我感谢任何输入，再次感谢。



我尝试了什么：



我一直在研究多个日期设计以及一个人应该如何进入数据库，只是试图弄清楚什么是最适合结构目的。

I have been trying to redesign portions of each table I have in my database. I have noticed many different structures including just having the full date in one column. However, I have been using the Day, Month, and Year columns so I could easily look for the particular year. Is it more beneficial to make a date column than to have the separate columns?

I appreciate any input, thanks again.

What I have tried:

I have been researching multiple date designs and how a person should be entered into a database, just trying to figure out what is the best for structure purposes.

推荐答案

如果你需要一年，那么使用单个DATE，DATETIME或DATETIME2列然后很容易要做的事： DATEPART [ ^ ]会在您需要时将它发送给您。



但是单独的列使得它很多，所以在这一个之后的所有日期或ORDER BY< date>要困难得多请求。



始终将值保存在适当的数据类型中，否则最终会很痛苦。例如，单个列不能包含无效日期，但是很容易出错，例如用单独的列交换日期和月份数字。当你试图使用它并找到糟糕的日期时，这会导致大量的问题 - 到那时它是认真的修复工作，因为你不知道有多少受影响或对原始数据的任何想法！

Use a single DATE, DATETIME, or DATETIME2 column if you need a year then it's easy to do: DATEPART[^] will give it to you when you need it.

But separate columns makes it a lot, lot harder to so "all dates after this one", or "ORDER BY <date>" requests.

Always keep values in the appropriate datatype, or it will end up being an pain later. For example, a single column can't contain an invalid date, but it's very easy to make a mistake and swap the day and month numbers with separate columns for example. That leads to massive problems later when you try to use it and find bad dates - and by then it's serious work to fix as you have no idea how many are affected or any idea of the original data!

这篇关于我应该将日期分为日，月和年列吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！