I work on a data warehouse that uses a technique like this for dates only (not the time portion). This was in the design from back when SQL Server didn't provide a native date-only type to use. I think that's 2005 and prior. Just as in this submission, we take an INT and put human-readable numbers in it to look like a date. It's like string concatenation. Example: DATETIME '2014-12-22 11:14:55.000' becomes INT '20141222'. I put quotes around it, but it's really a number and keeps all the beneficial properties of sorting, indexing, etc. The INT is only 4 bytes compared to using an 8 byte DATETIME type. There are drawbacks to doing this (see below), but on a big data warehouse the performance and storage benefits outweigh them. Back then, even Microsoft recommended doing this.

Now, however, this project is running on a version of SQL Server that supports the DATE type. This is only 3 bytes and requires no ugly conversion. I have tried to read up on it and never found a reason why a new project (without the legacy code) would still want to use the date-formatted INT versus the native DATE type.

So regarding this submission, I don't understand why it's beneficial to make a DATETIME into a BIGINT and keep the time portion. There's no upside and the following downsides:

• A conversion is required to store it

• The code will be less readable at any point that touches it

• It isn't compatible with the built-in date functions.

• Exporting this data requires a reverse-conversion because other applications won't understand it. For example, it makes it harder to do reports in SSRS, ETL with SSIS, or even to copy/paste results into Excel.

If there are any articles or studies that can show the performance benefits in doing this I would like to see them.