SQL: Datatype, Smalldatetime does not store second.

Smalldatetime datatype is less precision version of datatime datatype. Datetime variable takes 4 bytes while smalldatetime takes 2 bytes of memory. If you do not require precision to second level then, it's ok to use smalldatetime. But problem comes when you use datetime and smalldatetime variables interchangeably.

When you convert a datetime variable to smalldatetime, values with 29.998 seconds or lower are rounded down to nearest minute whereas values with 29.999 seconds or higher are rounded up to nearest minute. Here are two examples.

When following query is run in ISQLW (SQL Query Analyzer),

Select convert(smalldatetime,'2008-10-25 23:59:29.998')

The output is 2008-10-25 23:59:00.

Now, if we run the following query,

Select convert(smalldatetime,'2008-10-25 23:59:29.999')

The output is 2008-10-26 00:00:00.

The difference is obvious.

If we take only year, month and day from the variables and compare with operators like >, <, = then, the difference becomes 1 day.

This type of problem may not arise frequently.

But, if it comes, rounding of dates may not come to your mind at first.

After I notice, I have stopped using smalldatetime for just saving 2 bytes of memory.