AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

SQL Datetime: Everything you Need to Know

Published on Oct 31,2019 77 Views

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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 date 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 datetime type in more detail.

 

datetime Description

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

Property Value

Syntax

datetime

Usage

DECLARE @MyDatetime datetime

CREATE TABLE Table1 ( Column1 datetime )

Format

‘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

Calendar

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 the 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 values 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.

 

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.

Data TypeExample

time

12:35:29. 1234567

date

2007-05-08

smalldatetime

2007-05-08 12:35:00

datetime

2007-05-08 12:35:29.123

datetime2

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

datetimeoffset

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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
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.