In the business world, many reports are date and time sensitive. At times, all that matters is the date or the number of days difference from the current point and time.

This frequently creates the situation where the time portion of the datetime field type does not need and should not examine the time portion of this data type. When running a comparison in SQL Server 2005 for example, June 23rd at 1:00 A.M. is not the same as June 23rd at 12:00 A.M.

Therefore, it becomes necessary to compare the date portion of this type.

The official SQL Server Books online documentation states regarding the datetime type: "The first 4 bytes store the number of days before or after the base date: January 1, 1900."

Note the following examples executed in Query Analyzer application:

select convert(datetime, 39988) 
- output: 2009-06-26 00:00:00.000

select convert(datetime, 39988.5) 
- output:2009-06-26 12:00:00.000

select convert(datetime, 39988 + 14) 
- output:2009-07-10 00:00:00.000

select floor(convert(numeric(18,9),getdate())) 
- output:39985

select convert(datetime, floor(convert(numeric(18,9), getdate() + 14))) 
- output: 2009-07-07 00:00:00.000

The first example shows that a whole number produces a date and time that is 39988 days beyond January 1, 1900. It also shows that a whole number produces no time element in the datetime evaluation.

The second example show that providing a partial number (in this case 1/2) moves the hours to the time elapsed during the day (0.5 = 12 hours).

The third example shows adding 14 days to the previous input.

The fourth example shows getting the current system date and truncating the decimal portion.

The fifth example shows adding 14 days to the current date, truncating the time/decimal portion and then converting the numeric result back to the datetime type.

One consideration for these snippets is to create a SQL Server function that abstracts the conversion process.