datetime to integer

I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.Without knowing more my knee-jerk reaction would be to suggest sticking with the native datetime datatype. Can you give some more information describing what you are trying to accomplish?|||We're importing hundreds of thousands of records into hundreds of tables. All of it is time series data. The collected data is then analyzed using an external VB.NET program with dozens of custom analysis functions. The results of these calculations are saved into additional tables which are created on the fly. Some of the data in the resulting tables are reused in the custom functions. Results will eventually be compiled into a data warehouse from which additional analyses will be conducted (SQL Server Analysis Services, Data Mining, and SPSS). I should mention that the datetime values are unique (primary key candidate?); no table can have duplicate datetime values. Also, datetime values are essential in many of the analysis functions. I was told that if datetime values were converted to integers, the SQL Server 2005 performance improvement would more than outweigh the cost of conversion back and forth in the VB.NET functions.|||

Ahh, now we get down to it. When you say date/time is unique in all tables, my knee-jerk reaction is "Really? How are you assuring that" But know I am lead to believe that the source of the data is a data source that supplies more date/time precision than SQL Server records natively.

Key question: To what precision are you maintaining your date/time information?

Key question: How are you assuring that all date/time fields are unique? This is not a common guarantee in SQL Server.

|||

The data comes from several "clean" sources. The datetime field is set as a primary key. During import, checks for duplicate datetime values are made. After import, the data is cleaned again.

Most of the data is in one minute intervals.

When you say uniqueness is not a common guarantee, are you saying that setting a field to primary key or unique does not work properly in SQL Server?

|||If your granularity is finer than millisecond granularity then you will need an alternative to native datetime datatype. The native datetime datatype only tracks to 1/300th of a second.|||Most of the data is at one minute or higher (10 minute, hourly, etc.) levels. The possibility exists of sampling data as fine as one second intervals. Nothing will be done at the millisecond level.|||I think I would stick with native datetime; can we get additional opinions please?|||

If I recall correctly, internally SQL Server stores datetime values as a decimal number comprised of a (left side) four byte integer representing the number of days since Day 0 (Jan 01, 1900) , separating decimal, and a (right side) four byte integer representing the number of milliseconds since midnight.

So while not stored as an Integer, it is stored in the 'next best thing'.

|||Yes, however, that is NOT to the nearest millisecond but to the nearest 1/300 of a second; it is a legacy from Sybase.sql