51

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.

Matt
  • 7,022
  • 16
  • 53
  • 66
peter
  • 8,158
  • 21
  • 66
  • 119
  • 1
    This question definitely needs more information about the use case. But quite often a timezone is going to matter. The year 1990 started earlier in Australia than in the US. A simple int or string is usually not the right choice for dates. – Sebastiaan van den Broek Sep 27 '19 at 14:13

8 Answers8

45

regular 4 byte INT is way too big, is a waste of space!

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 as 4 bytes, which is way more than you need. You should use a two byte integer (smallint on SQL Server), which will conserve space.

Omar Trkzi
  • 130
  • 1
  • 2
  • 13
KM.
  • 101,727
  • 34
  • 178
  • 212
  • 4
    More often than not, wasting space using an int is preferable to using a smaller data type, since most modern processors are more efficient processing 4 byte ints (32 bit) than smaller data types. – Ender Oct 05 '13 at 20:11
  • 3
    @Ender, you're not really doing math with these values, it is more of a store and lookup process. You'll be able to store more values in ram if they are smaller, you'll be able to read more values from disk if they are smaller, etc. I couldn't find any articles that support your idea. http://dba.stackexchange.com/q/4968 – KM. Oct 07 '13 at 12:51
  • 1
    All programs compile to machine code at the end and numbers are loaded to registers. In modern CPU architectures all registers are 32 bit or 64 bit. So your RDBMS system will have to put those numbers into registers in order to make a comparison in order to satisfy the conditions in your query. And in the case of tiny int and small int, it has to make a conversion every single time, which is time consuming. You cant find an OFFICIAL article supporting my point OR your point since it is usually a trade off between CPU and IO since using int will increase index size and require more IO – Ender Oct 08 '13 at 14:31
  • 2
    @ender, there are always trade offs, I'd go with optimizing for IO (and smaller data types), since IO is usually the main bottleneck. – KM. Oct 08 '13 at 15:18
  • what about using char(4)? – Ed_ May 19 '15 at 13:26
  • 4
    @Weapon X, **why use 4 bytes to store a year?** a char(4) uses four bytes. My answer suggests a two byte int, which is a `smallint` on SQL Server and can store values in the range: -32,768 to 32,767, which is more than enough to store a year. You want to use as little space as possible because you can fix more data per page, and/or more data in memory and/or more index in memory, etc. **If I use smallint and you use char(4) you will use twice the space/memory to store the same data. It will take longer and more effort for the database to handle your `CHAR(4)` than my `SMALLINT`** – KM. May 19 '15 at 14:54
  • Ok got it, i was thinking the same since I am redesigning a database, and i don't see any problems in comparisons or searches using the smallint. – Ed_ May 19 '15 at 14:59
  • guys seriously? I can't speak for what this debate was like 10 years ago... but it's 2023; whatever speed difference exists between using a 4 byte vs 2 byte integer is **NEGLIGIBLE**; ESPECIALLY when we're talking about database systems, where the main bottleneck is connection speed. RAM consumption is more reasonable a concern, but IMO, the biggest focus should be the semantics of using each type: therefore, SMALLINT or CHAR(4)/CHAR(5) seems the best choice. – Krusty the Clown Jul 27 '23 at 15:46
35

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).

TheTXI
  • 37,429
  • 10
  • 86
  • 110
  • 24
    **Don't be lazy and "just use int".** Size your data type properly, a two byte int is a better choice. By storing 4 digit years in 4 bytes, you are wasting more resources than just disk space. Your system will forever be burdened by using twice the cache memory, pushing twice the amount of IO data, etc for this column. It is a no brainer, see: http://dba.stackexchange.com/q/4968 – KM. Jun 03 '15 at 12:43
  • 7
    B-b-but what about the year 32768 problem? (j/k) – nitro2k01 Oct 15 '18 at 02:00
  • 1
    @KM. I agree that this is an easy optimization that should be done, but let's not get too overwrought at the thought of not doing it. Not every project will see any significant difference. – Chuck Le Butt Sep 16 '19 at 14:12
  • 2
    @KM. I already said, "I agree that this is an easy optimization that should be done", so I don't really know what you're arguing with. I happen to work on smaller temporary systems that would never see any benefits from this change. I have 10 year's worth of such systems as history, too. What we build is designed for temporary use (maximum 6 months) and, even in the worst-case/best-case scenario, where a database kept growing, it would take another 10 decades (at least) before such an optimization would make any visible difference to us. – Chuck Le Butt Sep 20 '19 at 10:55
14

Hey,you can Use year() datatype in MySQL It is available in two-digit or four-digit format.

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

user786900
  • 149
  • 1
  • 2
3

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.

Qubits
  • 303
  • 3
  • 8
2

Just a year, nothing else ? Why not use a simple integer ?

Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
  • 16
    Hey! Why are you trying to make things so simple? We will run out of SO questions! – Joe Phillips Mar 30 '09 at 16:19
  • Because in some situations data ends up auto-formatted according to db column type, so you end up with years displayed like "2,001" and "-56" instead of "2001" and "56 BC" – mgraham Sep 28 '22 at 15:30
2

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

Ender
  • 213
  • 1
  • 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..

Rawheiser
  • 1,200
  • 8
  • 17
0

I don't think using an integer or any subtype of integer is a good choice. Sooner or later you will have to do other date like operations on it. Also in 2019 let's not worry too much about space. See what those saved 2 bytes costed us in 2000.

I suggest use a date of year + 0101 converted to a true date. Similarly if you need to store a month of a year store year + month + 01 as a true date.

If you have done that you will be able to properly do "date stuff" on it later on

Saad Ahmad
  • 393
  • 1
  • 7