MS SQL Training (32 Blogs) Become a Certified Professional
AWS Global Infrastructure


Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)

SQL Datetime: Everything you Need to Know

Last updated on Jun 26,2024 152.2K Views

At times dealing with date and time in SQL can be quite tricky. While date and time are actually completely different data types, they are often merged into a datetime date data type. SQL dates and times alone are pretty simple but merging the two can be one of the most painful tasks. In this article, will learn about SQL datetime type in detail.


What is the datetime data type?

In SQL, datetime data type is used for values that contain both date and time. Microsoft defines it as a date combined with a time of day with fractional seconds that is based on a 24-hour clock.

SQL specifically, has many data types that combine both the date and time representations making things more complex. The most widely used one is the DATETIME as it has been present since the earlier versions of SQL. SQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh: mm: ss’ format. The supported range is ‘1753-01-01 00:00:00’ to ‘9999-12-31 23:59:59.997’. Let’s explore the datetime type in more detail.


datetime Description

Please check the following table to know more about the SQL datetime type.

Property Value




DECLARE @MyDatetime datetime

CREATE TABLE Table1 ( Column1 datetime )


‘YYYY-MM-DD hh: mm: ss.nnn

Time range

00:00:00 through 23:59:59.997

Element ranges

  • YYYY is four digits from 1753 through 9999 which represent a year.
  • MM is two digits, ranging from 01 to 12, which represents a month in the specified year.
  • DD is two digits, ranging from 01 to 31 depending on the month, which represents a day of the specified month.
  • hh is two digits, ranging from 00 to 23, that represent the hour.
  • mm is two digits, ranging from 00 to 59, which represents the minute.
  • ss is two digits, ranging from 00 to 59, which represents the second.
  • n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

Storage size

8 bytes

Default value

1900-01-01 00:00:00


Gregorian (Does include the complete range of years.)

Note: The details above apply for datetime type in Transact-SQL and SQL Server. 

So, that’s datetime in SQL. But what would you do if you have other date & time types and you have to convert them to datetime type?


Converting Other Date and Time Types to Datetime Data Type

The datetime data type in SQL includes the date and time, with a 3 digit fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds. So, when you convert a date or time value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time. This part of the article explains what occurs when other date and time data types are converted to the datetime data type.

For a detailed, You can even check out the details of relational databases, functions, and queries, variables, etc with the SQL course online.

Example1: Implicit conversion between date and datetime

DECLARE @date date = '2020-12-01';  
DECLARE @datetime datetime = @date;


@datetime               @date  
------------------------- ----------  
2016-12-21 00:00:00.000 2016-12-21  


Example2: Implicit conversion between date and datetime using CAST()

DECLARE @thedate date = '2020-12-01'
  @thedate AS 'date',
  CAST(@thedate AS datetime) AS 'datetime';


@datetime               @date  
------------------------- ----------  
2016-12-21 00:00:00.000 2016-12-21  


Example3: Implicit conversion from smalldatetime to datetime

When the conversion is from smalldatetime type, the hours and minutes are copied. The seconds and fractional seconds are set to value 0. The following code shows the results of converting a smalldatetime value to a datetime value.

DECLARE @smalldatetime smalldatetime = '2020-12-01 12:32';  
DECLARE @datetime datetime = @smalldatetime;  
SELECT @datetime AS '@datetime', @smalldatetime AS '@smalldatetime'; 


@datetime               @smalldatetime  
------------------------- -----------------------  
2016-12-01 12:32:00.000 2016-12-01 12:32:00 

Similarly, you can convert other date & time types to datatime type either implicitly or using cast() and convert() methods. For your reference check out the table below to familiarize yourself with the formats of all the date and time types.

Data TypeExample


12:35:29. 1234567




2007-05-08 12:35:00


2007-05-08 12:35:29.123


2007-05-08 12:35:29. 1234567


2007-05-08 12:35:29.1234567 +12:15

With this, we have come to the end of this article. I hope you are clear about the content discussed here. Make sure you practice as much as possible and revert your experience.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this ‘Procedures in SQL; article and we will get back to you.

Upcoming Batches For Microsoft SQL Server Certification Training Course
Course NameDateDetails
Microsoft SQL Server Certification Training Course

Class Starts on 3rd August,2024

3rd August

SAT&SUN (Weekend Batch)
View Details

Join the discussion

Browse Categories

webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

SQL Datetime: Everything you Need to Know