Recently while working on a new feature with another member of the team that I’m on, a little bit of controversy came up. Part of the new feature required that we store a time of day. Now if we were using SQL Server 2008 there wouldn’t be a problem as there is a data type that is perfectly appropriate for storing time, it’s called time. But our environment is currently using SQL Server 2005, so we have to compromise.

When deciding what kind of data type to use in your database, you need to select a data type that allows you to store data without having to manipulate it too much. What does that mean? It’s quite simple really, a phone number (in the United States) is composed of 10 digits, however you would not want to store the phone number as an integer because when you view the phone number as a whole it is actually not an integer. Certain elements of a phone number can actually have a leading zero which you cannot have in an integer (it’s truncated). Due to a lack of a “PhoneNumber” data type you have to revert back to the most appropriate data type which in this case would be a “CHAR” data type. Why? Two reasons, the length of each segment of the phone number is known and at least one segment of a phone number allows for leading zeros. If we were to store the phone number as an integer we’d have to do extra code gymnastics when reading it from the database (to replace possible leading zeros).

This brings me to storing only time. As mentioned earlier there is no data type in SQL Server 2005 that fits perfectly for storing time, so we have to compromise. In my case there were three options. They are as follows…

Store the time value as a string. With a defined CHAR or VARCHAR field you can most defiantly store a value that appears to be a time value, however there are some downsides. Since this field would be a string field the database would accept any value within the allotted character width to be placed into the column. Next, in order to do any time specific operations against the data in the column would require extra code to convert the string into a more appropriate data type.

The next suggestion was simply store the time of day inside of two INT fields, one for hours and one for minutes. This option in my opinion was a little better than storing in strings as it narrows the possibility of storing invalid values, after all you can’t store a string in an integer field. However, this option has the same draw backs as storing the value in a string. If you are planning on manipulating the time value stored in the two database fields using a stored procedure or ad-hoc query you must convert to the hour and minute fields to strings and then concatenate them together with a bogus date and then convert the concatenated string to a DATETIME value. Seems a like a lot of work to me.

The third suggestion is to store the time of day in a DATETIME field and simply ignore the date part of the value. This would save us the hassle of having to convert the value to another data type before performing DATETIME related operations against the value. The downside to this option is that you are storing data that you don’t need, namely the date, and all consumers of the data would have to know that the date indicated in the database is not actually used.

In my opinion the best way to approach storing time to a database would the DATETIME type as the least amount of work is needed in order to make it work. Yes you have the issue of having to know that the date portion of the field is ignored but this issue is no different than having to maintain a proper format in a non date time data type.

Long story short, just use DATETIME.