Hi all...


A datetime data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds.

On the other hand, a smalldatetime data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

Smalldatetime:

Minimum value of smalldatetime is january 1, 1990 and the maximum
value is june 6, 2079.

Time accuracy is up to a minute.

Storage size is 4 bytes.

Datetime:

Minimum value of datetime is jan 1, 1753 and the maximum value is
december 31, 9999.

Time accuracy is one three-hundredth of a second.

Storage size is 8 bytes

smalldatetime is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates. datetime is used if the time of the day is needed and up to the second accuracy is required.


Thanks...