Database: Storing Dates as Numeric Values

I'm considering storing some date values as ints. i.e 201003150900

Excepting the fact that I lose any timezone information, is there anything else I开发者_StackOverflow社区 should be concerned about with his solution? Any queries using this column would be simple 'where after or before' type lookups. i.e Where datefield is less than 201103000000 (before March next year).

currently the app is using MSSQL2005. Any pointers to pitfalls appreciated.

Using a proper datetime datatype will give you more efficient storage (smalldatetime consumes 4 bytes) and indexing, and will give you semantics that will be easier to develop against. You'd have to come up with a compelling argument to not use them.

Why wouldn't you use proper UNIX timestamps? They're just ints too, but they're not nearly as wide as 201103000000.

Just use the DATETIME or SMALLDATETIME datatypes they are more flexible.

The only reason to do it the way you suggest is so that you have a time dimension member name for a business intelligence tool. If that is what you intend to use it for, then it makes sense.

Otherwise, use the built-in time types as others have pointed out.