What would be the best to store date + hour + timezone in SQL? i dont need a higher precision (minutes, seconds, ms..) the primary reason for this is to reduce memory use and calculation time on big datasets. or should'nt this a concern at all?


In SQL Server, always store dates using a datetime, datetimeoffset or datetime2 datatype. Storing them as anything else invariably results in needless CPU spent converting the column into some kind of date.

The possible exception to this might be if you want to store just the year, which could be a tiny integer. However, since you need hour and timezone precision, use the datetimeoffset data type. datetimeoffset(0) will give you precision down to the second, which you don't require, however you'll be guaranteed to never have an invalid date stored, and you'll know that the data is time-zone compatible.

datetimeoffset(0) occupies 8 bytes, unless you're storing a lot of dates in memory, this shouldn't be considered wasteful in light of the advantages conferred by using the correct data type.

You may be interested in reading this answer, which talks about some common problems storing date/time data incorrectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.