Is there a performance or storage difference to consider when using Date vs DateTime?

Question

When using SQL Server & ASP.NET, is there a performance / storage consideration when using Date vs DateTime?

Even if I don't need it, I've been using DateTime for most things

Answer 1

DateTime takes 8 bytes per value

Date is 3 bytes.

I can't speak for low level performance; however in general we've found it a mistake to store values as DateTime by default. Sooner or later you run into the UTC issue and have to start working out offsets for dates that have 00:00:00.000 in the time portion!

If you're just storing dates I'd stick to the Date datatype; you'll fit more rows per page and save yourself a lot of hassle

Answer 2

Depends how many rows you're storing, and what you're using it for. Date is 3 bytes, DateTime is 8 bytes. Can quickly add up when you have billions of rows of data, or are using it for an index. Naturally there is a difference in the resolution of the value stored too. There are other date-types between date and datetime too such as smalldatetime that are more compact, again with different compromises.

SQL Date/Time Types Documentation