SQL DATETIME Vs SMALLDATETIME Datatypes

The main differences between SQL Server's DATETIME and SMALL DATETIME data types.

Although both data types are used to store date and time values, there are some distinctions between them.

In most circumstances, it's advisable to avoid both types and instead use DATETIME2 (Microsoft also recommends this).

In any case, here's how these two data types compare.

SQL DATETIME Datatype

The DATETIME data type is created by converting date and time data types.

The year, month, and day are copied when converting from date. 00:00:00.000 is the time component's value.

Example: The results of converting a date value to a datetime value are shown in the code below.

Sqlserver

DECLARE @date date = '24-03-2017';  
DECLARE @datetime datetime = 24-03-2017;  
SELECT @datetime AS 'Datetime', @date AS '@date';  

The above statement we first pass date value to date datatype then datetime datatype

Output: The result of datetime will be

SQL SMALLDATETIME DATATYPE

The date and time data types of the SMALLDATETIME data type are translated to other date and time data types.

The year, month, and day are copied for a date conversion.

Example: The results of converting a SMALLDATETIME value to a date value are shown in the code below.

Sqlserver

DECLARE @smalldatetime smalldatetime = '1940-07-13 12:20:30  
DECLARE @date date = '1940-12-13'; 
SELECT @smalldatetime AS 'Smalldatetime', @date AS 'Date';

This function have limited value to store.In this example we cannote the value Less than year of 1900 & greater than 9999.

Result: The output will be

The hours, minutes, and seconds are replicated when converting to time(n).

The fractional seconds have been set to 0 by default.

The results of converting a smalldatetime value to a time(4) value are shown in the following code.

SQL Difference Between DATETIME And SMALL DATETIME Datatype

Below table contains contrast in datetime vs small datetime:-