SQL Server 2008 - date question

[fredericofonseca]

Hi all,


Quick question for those of you that work with datawarehouses.


Would there be any situation where it would be advisable to store a date (just date, not time) as a integer, and if so why would that be. Please do not consider the possibility of having a primary key of a date table to be the corresponding date converted to a integer. I'm only interested on cases like storing a effective date or a payment date as a integer versus a date type.
Tables with up to 500m records, with 4-5 date fields


Storage should not be of importance as date datatime takes only 3 bites versus 4 bites that a integer would require.

This in SQL server 2008 R2 - no consideration required for portability to previous versions.


thanks




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[flapeyre]

All dates are datetime here. If the date field in question has no time component, no big deal; you simply display in a format without the time in the report or whatever.

I once worked on a wacky system (not SQL) that had the same date in three different numeric fields - one MMDDYY, one YYMMDD, and one Julian (I truly despise working with Julian dates). I shudder whenever I remember that system.

-- Francis
In Deo nos confídimus.
Ceteris pariatur.

 

[fredericofonseca]

anyone else?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

I don't work with data warehouses, and my DB is tiny compared to today's standards. With all that, I can't think of a good reason why you would want to use an integer to store a date.

If storage was important, then you could make up your own system and use a small int (2 bytes). Since the range is relatively small (-32k to 32k) you would need to pick a good reference date for 0, like Jan 1, 2000. But... I wouldn't. This would just muck up the data for very little gain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom