Difference Between DateTime and SmallDateTime in SQL Server

SQL Server datatypes are used to store date and date and time values in the database, there are various types of date data types available in the SQL. Whenever we manage data in the SQL server database, it’s often very important to choose the right to store the date and time.

The following two data types are used for storing the date and time in SQL Server:

1. DateTime
2. SmallDateTime

Both the above choices have their impact on how much storage is required to store the data, how fast the queries can be, and the accuracy of the data as well. so in this article, we’ll understand the key differences between both of the data types and how they impact the way we store data in the database.

Difference Between SMALLDATETIME and DATETIME Date Data Types

Feature	smallDatetime	DateTime
Accuracy	One minute	Rounded to increments of .000, .003, or .007 seconds.
Fractional second precision	No	Yes
Time Zone Offset	None	None
Storage Size	4 Bytes, Fixed.	8 Bytes, Fixed.
Character length	19 positions maximum	19 positions minimum
Time Range	00:00:00 through 23:59:59	00:00:00 through 23:59:59.997
Date Range	1900-01-01 through 2079-06-06	1753-01-01 through 9999-12-31

Note – The Oracle documentation sometimes uses DATETIME when it is talking about the DATE datatype and the TIMESTAMP data type together, and since we are focused on DATETIME and SMALLDATETIME (which are part of MS SQL Server) we will be using the MS SQL Server for running the queries.

Examples related to DateTime

Let’s understand the DateTime data type with a simple example given below:

Query

-- Creating a table with DateTime data type
CREATE TABLE DateTimeExample (
 EventName NVARCHAR(50),
 EventDate DateTime
)

-- Inserting data with DateTime
INSERT INTO DateTimeExample (EventName, EventDate)
VALUES ('Meeting', '2023-10-30 14:30:00')

-- Querying data
SELECT * FROM DateTimeExample

Program Explanation: Here, in the above query first we create a table named ‘DateTimeExample’ with two columns (EventName, EventDate) where the ‘EventDate’ holds the DateTime data type. Now when we insert data into this table.

Output

Here, we can see that the EventDate shows the time with milliseconds if the user defines it, which is specifically the purpose of using the DateTime data type, so if we want to show time with further accuracy then we can use the DateTime data type.

Example Related to SmallDateTime

Let’s understand the SmallDateTime data type with a simple example given below:

Query

-- Creating a table with SmallDateTime data type
CREATE TABLE SmallDateTimeExample (
 EventName NVARCHAR(50),
 EventDate SmallDateTime
)

-- Inserting data with SmallDateTime
INSERT INTO SmallDateTimeExample (EventName, EventDate)
VALUES ('Appointment', '2023-10-30 14:30:00')

-- Querying data
SELECT * FROM SmallDateTimeExample

Code explanation:

Again, we have created a table named ‘SmallDateTimeExample’ where we have two columns, EventName and EventDate and we have given the ‘EvenDate’ column the ‘SmallDateTime’ data type and then we have inserted some values into the table.

Output

Here we get to understand the key difference between the SQL Server DateTime and SmallDateTime data types in MS Server, the ‘SmallDateTime’ does not provide as much accuracy as compared to the ‘DateTime’ data type hence it’s used for storing data and time data values where fractional parts of the milliseconds are optional.

Conclusion

In conclusion, both of the date data types are useful and have their own functionality, if the database administrator needs to have more data accuracy then they can opt for the 'DateTime' datatype otherwise the 'SmallDateTime' can be used.