SQL Datetime: Everything you Need to Know

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?
• datetime Description
• Converting Other Date and Time Types to the datetime Data Type

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.

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;

Result

 
@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'
SELECT 
  @thedate AS 'date',
  CAST(@thedate AS datetime) AS 'datetime';

Result

 
@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'; 

Result

@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.

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.