SQL DATETIME Vs DATETIME 2 Datatype

The distinction between Datetime and Datetime2 functions will be discussed in this post. Instead of Datetime , Microsoft recommends using Datetime2 . As a result, we'll compare and contrast them in this section.

SQL DATETIME Datatype

The DATETIME data type specifies a fractional second date and time.

The DateTime data type is the most popular for storing date and time information together.

Dates from January 1, 1753, to December 31, 9999 are supported by DATETIME.

1900-01-01 00:00:00 is the default value. The time is shown in 24-hour format.

Example: A table with a DATETIME column.

Take note of how SQL Server takes a variety of date and time formats as input.

These formats are transformed to 'YYYY-MM-DD HH:MM:SS', which is the internal format.

CREATE TABLE Customer  
( 
  CustomerName VARCHAR(100) NOT NULL,
  Product VARCHAR(100) NOT NULL,
  OrderDateTime DATETIME
);


INSERT INTO Customer VALUES ('Lilly', 'Kitkat', '2020-09-25 9:55:12');
INSERT INTO Customer VALUES ('Vishal', 'Pasta', '2019/08/03 9AM');
INSERT INTO Customer VALUES ('Mani', 'Hide & seek Cookies', '2022-05-14 12:10');

SELECT * FROM Customer;

This datatype doesn't allow the year of below 1900.

Output: The output of above example is

SQL DATETIME 2 Datatype

The DATETIME2 type is an extension of the original Datetime type.

The DATETIME2 data type specifies a fractional second date and time.

The datetime2 variable in this case has a scale of 7, which signifies 7 decimal places.

Dates from 0001-01-01 to 9999-12-31 are supported by DATETIME2.

1900-01-01 00:00:00 is the default value. A 24-hour clock is used to keep track of time.

Example: The example creates a table with a DATETIME2 column.

CREATE TABLE Customer
( 
 
  CustomerName VARCHAR(100) NOT NULL,
  ProductName VARCHAR(100) NOT NULL,
  OrderDateTime DATETIME2
);



INSERT INTO Customer VALUES ('Ravi', 'Quaker Oats', '2021/12/10 8AM');
INSERT INTO Customer VALUES ('Jeni', 'Good day Cookies', '2022-05-14 13:12');
INSERT INTO Customer VALUES ('Karthi', 'Frutie', '2022-9-2');


SELECT * FROM Customer;

The datetime2 datatype doesn't allow daterange above the year of 9999

Output: The output of above example is

SQL Difference Between DATETIME And DATETIME2 Datatype

In below table we discuss the distinguish datetime & datetime2 datatype:-