Search code examples
sqldatetimedata-warehouse

SQL Server Storing DateTime as Integer

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?

Solution

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