Let us learn more about the SQL server smalldatetime and how to use it. With the support of our MSSQL support services at Bobcares, we can give you a detailed note on how to use it.

DateTime & SmallDateTime in SQL Server

DateTime and SmallDateTime are data types that hold both date and time in the SQL server. The time is measured in 24-hour increments.

SQL SMALLDATETIME Data Type

The SMALLDATETIME data type gives a date and time of day. Dates from 1900-01-01 to 2079-06-06 are supported by SMALLDATETIME.

The default value is 1900-01-01 00:00:00.

The seconds are always set to zero, and fractional seconds are not taken into account.

SmallDateTime in SQL Server

Fractional seconds are ignored, and the seconds are always set to zero.

Any numbers entered in seconds that are up to 29.998 are rounded to the closest minute. Over 29.999 seconds, the numbers are rounded up.

DateTime Vs SmallDateTime

The table below illustrates the distinctions between DateTime and SmallDateTime.

Syntax

datetime

smalldatetime

Usage

CREATE TABLE Table1 ( Column1 datetime )

CREATE TABLE Table1 ( Column1 smalldatetime )

Date range

1753-01-01 to 1999-12-01

1900-01-01 to 2079-06-06

Time range

00:00:00.000 through 23:59:59.997

00:00 through 23:59

Storage size

8 bytes

4 bytes, fixed

Accuracy

Rounded to increments of .000, .003, or .007 seconds

One minute

Default value

1900-01-01 00:00:00

1900-01-01 00:00:00

DateTime Examples

Creating DateTime & SmallDateTime Columns

The table below illustrates the distinctions between DateTime and SmallDateTime. 

123456

CREATE TABLE Table1 (    Column1 datetime ,   Column2 SmallDateTime)

Creating DateTime & SmallDateTime Columns

Inserting Values to DateTime & SmallDateTime Columns:

12345678910

Insert into Table1 (Column1, Column2) Values ('2020-12-21 12:40:53','2020-12-20 12:40:53' )Select * From Table1----------------------- ------------------- 2020-12-21 12:40:53.000 2020-12-20 12:41:00

Local Variables

123456789

DECLARE @varDateTime datetimeDECLARE @varSmalldatetime smalldatetime;SET @varDateTime = '2020-12-21 12:40:53';SET @varSmalldatetime ='2020-12-21 12:40:53';SELECT @varDateTime,   @varSmalldatetime;

Default Date & Time

After that, both DateTime & SmallDateTime always contains date and time. If we do not provide the time, then it defaults to 00:00:00.

123456789

Insert into Table1 (Column1, Column2) Values ('2020-12-21','2020-12-21' )Column1                 Column1         ----------------------- --------------------2020-12-21 00:00:00.000 2020-12-21 00:00:00

If no dates are specified, the date 1900-01-01 is used as the default.

123456789

Insert into Table1 (Column1, Column2) Values ('20:50:50.3','20:50:50.3' )Column1                 Column1         ----------------------- --------------------1900-01-01 20:50:50.300 1900-01-01 20:51:00

Range

Both of these data formats have a restricted range. The smallest date you can enter into a DateTime is 1753-01-01, while the smallest date you can insert into a SmallDateTime is 1900-01-01.

12345678910

Insert into Table1 (Column1, Column2) Values ('1753-01-01','1900-01-01' )Select * from Table1Column1                 Column2----------------------- ------------------- 1753-01-01 00:00:00.000 1900-01-01 00:00:00

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now learned more about the SQL server smalldatetime. We have also learned how to use it with the support of our MSSQL support services at Bobcares.