I need to insert a year(eg:1988 ,1990 etc) in a database. When I used Date or Datetime data type, it is showing errors. Which datatype should I use.
我需要在数据库中插入一年(如:1988、1990等)。当我使用Date或Datetime数据类型时，它显示出错误。我应该使用哪个数据类型。
7 个解决方案
#1
26
If you need to store a year in the database, you would either want to use an Integer datatype (if you are dead set on only storing the year) or a DateTime datatype (which would involve storing a date that basically is 1/1/1990 00:00:00 in format).
如果您需要在数据库中存储一年，您可能需要使用整数数据类型(如果您完全设置为只存储年份)或DateTime数据类型(这将涉及存储格式为1/1/1990 00:00的日期)。
#2
26
regular 4 byte INT is way to big, is a waste of space!
普通的4字节整数是很大的，是浪费空间!
You don't say what database you're using, so I can't recommend a specific datatype. Everyone is saying "use integer", but most databases store integers at 4 bytes, which is way more than you need. You should use a two byte integer (smallint on SQL Server), which will conserve space better.
你没有说你正在使用哪个数据库，所以我不能推荐一个特定的数据类型。每个人都在说“使用整数”，但是大多数数据库以4字节存储整数，这远远超出了您的需要。您应该使用一个双字节整数(SQL Server上的smallint)，这样可以更好地节省空间。
#3
9
Hey,you can Use year() datatype in MySQL It is available in two-digit or four-digit format.
嘿，你可以在MySQL中使用year()数据类型它有两位数或四位数的格式。
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
注:四位数格式允许的值:1901 - 2155。两位数格式的值:70到69，代表1970到2069年
#4
2
Storing a "Year" in MSSQL would ideally depend on what you are doing with it and what the meaning of that "year" would be to your application and database. That being said there are a few things to state here. There is no "DataType" for Year as of 2012 in MSSQL. I would lean toward using SMALLINT as it is only 2 bytes (saving you 2 of the 4 bytes that INT demands). Your limitation is that you can not have a year older than 32767 (as of SQL Server 2008R2). I really do not think SQL will be the database of choice ten thousand years from now let alone 32767. You may consider INT as the Year() function in MSSQL does convert the data type "DATE" to an INT. Like I said, it depends on where you are getting the data and where it is going, but SMALLINT should be just fine. INT would be overkill ... unless you have other reasons like the one I mentioned above or if the code requirements need it in INT form (e.g. integrating with existing application). Most likely SMALLINT should be just fine.
在MSSQL中存储“年”在理想情况下取决于您正在使用它做什么，以及“年”对应用程序和数据库的意义。话虽如此，这里有几件事要说明。MSSQL中2012年没有“数据类型”。我倾向于使用SMALLINT，因为它只有2个字节(节省4个字节中的2个)。您的限制是不能超过32767 (SQL Server 2008R2)。我真的不认为SQL会成为未来一万年的首选数据库，更别提32767了。您可以将INT作为MSSQL中的Year()函数将数据类型“DATE”转换为INT，就像我说的，这取决于您在哪里获取数据以及数据的去向，但是SMALLINT应该没问题。INT将是多余的…除非您有其他原因，如上面提到的，或者代码需求需要以INT形式(例如，与现有应用程序集成)。最可能的是，SMALLINT应该没问题。
#5
1
Just a year, nothing else ? Why not use a simple integer ?
仅仅一年，什么都没有?为什么不使用一个简单的整数呢?
#6
1
Use integer if all you need to store is the year. You can also use datetime if you think there will be date based calculations while querying this column
如果只需要存储年份，则使用integer。如果您认为在查询此列时将有基于日期的计算，那么也可以使用datetime
#7
0
Storage may be only part of the issue. How will this value be used in a query?
存储可能只是问题的一部分。如何在查询中使用这个值?
Is it going to be compared with another date-time data types, or will all the associated rows also have numeric values?
它将与其他日期-时间数据类型进行比较，还是所有关联的行都具有数值?
How would you deal with a change to the requirements? How easily could you react to a request to replace the year with a smaller time slice? i.e. Now they want it broken down by quarters?
您将如何处理需求的变更?对于用更小的时间片取代一年的请求，你能有多容易做出反应?也就是说，现在他们想把它分成四份?
A numeric type can be easily used in a date time query by having a look-up table to join with containing things like the start and stop dates (1/1/X to 12/31/x), etc..
数字类型可以在日期时间查询中方便地使用，方法是使用一个查找表与包含开始日期和停止日期(1/1/X到12/31/ X)等内容的表相连接。
#1
26
If you need to store a year in the database, you would either want to use an Integer datatype (if you are dead set on only storing the year) or a DateTime datatype (which would involve storing a date that basically is 1/1/1990 00:00:00 in format).
如果您需要在数据库中存储一年，您可能需要使用整数数据类型(如果您完全设置为只存储年份)或DateTime数据类型(这将涉及存储格式为1/1/1990 00:00的日期)。
#2
26
regular 4 byte INT is way to big, is a waste of space!
普通的4字节整数是很大的，是浪费空间!
You don't say what database you're using, so I can't recommend a specific datatype. Everyone is saying "use integer", but most databases store integers at 4 bytes, which is way more than you need. You should use a two byte integer (smallint on SQL Server), which will conserve space better.
你没有说你正在使用哪个数据库，所以我不能推荐一个特定的数据类型。每个人都在说“使用整数”，但是大多数数据库以4字节存储整数，这远远超出了您的需要。您应该使用一个双字节整数(SQL Server上的smallint)，这样可以更好地节省空间。
#3
9
Hey,you can Use year() datatype in MySQL It is available in two-digit or four-digit format.
嘿，你可以在MySQL中使用year()数据类型它有两位数或四位数的格式。
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
注:四位数格式允许的值:1901 - 2155。两位数格式的值:70到69，代表1970到2069年
#4
2
Storing a "Year" in MSSQL would ideally depend on what you are doing with it and what the meaning of that "year" would be to your application and database. That being said there are a few things to state here. There is no "DataType" for Year as of 2012 in MSSQL. I would lean toward using SMALLINT as it is only 2 bytes (saving you 2 of the 4 bytes that INT demands). Your limitation is that you can not have a year older than 32767 (as of SQL Server 2008R2). I really do not think SQL will be the database of choice ten thousand years from now let alone 32767. You may consider INT as the Year() function in MSSQL does convert the data type "DATE" to an INT. Like I said, it depends on where you are getting the data and where it is going, but SMALLINT should be just fine. INT would be overkill ... unless you have other reasons like the one I mentioned above or if the code requirements need it in INT form (e.g. integrating with existing application). Most likely SMALLINT should be just fine.
在MSSQL中存储“年”在理想情况下取决于您正在使用它做什么，以及“年”对应用程序和数据库的意义。话虽如此，这里有几件事要说明。MSSQL中2012年没有“数据类型”。我倾向于使用SMALLINT，因为它只有2个字节(节省4个字节中的2个)。您的限制是不能超过32767 (SQL Server 2008R2)。我真的不认为SQL会成为未来一万年的首选数据库，更别提32767了。您可以将INT作为MSSQL中的Year()函数将数据类型“DATE”转换为INT，就像我说的，这取决于您在哪里获取数据以及数据的去向，但是SMALLINT应该没问题。INT将是多余的…除非您有其他原因，如上面提到的，或者代码需求需要以INT形式(例如，与现有应用程序集成)。最可能的是，SMALLINT应该没问题。
#5
1
Just a year, nothing else ? Why not use a simple integer ?
仅仅一年，什么都没有?为什么不使用一个简单的整数呢?
#6
1
Use integer if all you need to store is the year. You can also use datetime if you think there will be date based calculations while querying this column
如果只需要存储年份，则使用integer。如果您认为在查询此列时将有基于日期的计算，那么也可以使用datetime
#7
0
Storage may be only part of the issue. How will this value be used in a query?
存储可能只是问题的一部分。如何在查询中使用这个值?
Is it going to be compared with another date-time data types, or will all the associated rows also have numeric values?
它将与其他日期-时间数据类型进行比较，还是所有关联的行都具有数值?
How would you deal with a change to the requirements? How easily could you react to a request to replace the year with a smaller time slice? i.e. Now they want it broken down by quarters?
您将如何处理需求的变更?对于用更小的时间片取代一年的请求，你能有多容易做出反应?也就是说，现在他们想把它分成四份?
A numeric type can be easily used in a date time query by having a look-up table to join with containing things like the start and stop dates (1/1/X to 12/31/x), etc..
数字类型可以在日期时间查询中方便地使用，方法是使用一个查找表与包含开始日期和停止日期(1/1/X到12/31/ X)等内容的表相连接。