SQL Server Storing DateTime as Integer

Question

I am adding some tables to a data warehouse and want to store datetime information into an int field (the time part is not important to me)

Note that I'm not storing as 20150123, I just store it as pure integer using

CAST(field as int)

that stores 2015-06-18 12:57:47.833 as 42172

I can easily add a day by +1 and add a week by adding 7 to the field. however adding a month is not straight forward. Also whenever I need a date representation of the data, I can cast it to datetime.

Just want to know what pros and cons you see on this?

Answer 1

Converting between an int and a datetime works just fine. It's well defined what the conversion does, so there is nothing mysterious going on that might suddenly change or stop working.

Let's look at some aspects:

• Comparable: Yes; you can still just compare the numbers to tell which date is earlier.
• Sortable: Yes; you can simply sort on the int and it's sorted by date.
• Readable: No; you have to convert it to a datetime to make any sense of it.
• Self-explanatory: No; you have to know what the number represents to do anything with it.
• Portable: Yes; you can do the same conversion in any system that supports date arithmetics.

You can for example do the same conversion from int to DateTime in C#:

DateTime d = new DateTime(1900, 1, 1).AddDays(42172);

When it comes to storage space, there isn't really any advantage. An int uses four bytes and a datetime uses eight bytes. If you really need to preserve space, you should rather use the date type, which uses only three bytes.

Answer 2

Cons: when you cast it to int, you loose the time portion of the datetime, and end up with only the date part stored. You save 4 bytes (8 bytes for datetime, 4 bytes for int), but you lose precision. By casting to date, you loose just as much precision, but you save 5 bytes (3 bytes for date). So if the goal is saving storage space I would recommend cast to date.

If the objective is easy manipulation, use the DateAdd and DateDiff functions, they work on both date and datetime. Not as straightforward as +1 or -7, but not much more complex either, and basically the only way to add months or years without extensive coding.

Pros: if you work with Excel no conversion is needed between Excel and the database, this is Excel's default date format, made readable by Excel, with all Excel's date functions working on it