Have you ever wondered why SQL Server does not have separate DATE and TIME datatypes? This question has been asked by many, including project managers who have a good understanding of relational database management system (RDBMS) concepts. In this article, we will explore the reasons behind this and how SQL Server 2008 introduced separate DATE and TIME datatypes.

DATE Datatype

In SQL Server 2005 and earlier versions, there was no dedicated DATE datatype. Instead, the DATETIME datatype was used to store both date and time values. However, it was possible to extract only the date portion using the CONVERT function.

Let’s take a look at an example:

DECLARE @Date AS DATETIME
SET @Date = GETDATE()

SELECT @Date AS OriginalDate, CONVERT(VARCHAR(10), @Date, 111) AS CastDate

The result would be:

OriginalDate         CastDate
-------------------  ----------
2007-12-22 17:48:14  2007/12/22

As you can see, we were able to extract the date portion using the CONVERT function with the appropriate style code.

TIME Datatype

Similar to the DATE datatype, SQL Server 2005 and earlier versions did not have a dedicated TIME datatype. The time portion was stored within the DATETIME datatype, and it was possible to extract only the time using the CONVERT function.

Here’s an example:

DECLARE @Time AS DATETIME
SET @Time = GETDATE()

SELECT @Time AS OriginalTime, CONVERT(VARCHAR(10), @Time, 108) AS CastTime

The result would be:

OriginalTime         CastTime
-------------------  --------
2007-12-22 17:48:57  17:48:57

Again, we were able to extract the time portion using the CONVERT function with the appropriate style code.

SQL Server 2008 and Beyond

In response to the demand for separate DATE and TIME datatypes, Microsoft introduced these datatypes in SQL Server 2008. This allowed for more precise storage and manipulation of date and time values.

With the introduction of the DATE datatype, you can now store only the date portion without the time component. Similarly, the TIME datatype allows you to store only the time portion without the date component.

Here’s an example of using the DATE datatype:

DECLARE @Date AS DATE
SET @Date = GETDATE()

SELECT @Date AS OriginalDate

The result would be:

OriginalDate
------------
2007-12-22

And here’s an example of using the TIME datatype:

DECLARE @Time AS TIME
SET @Time = GETDATE()

SELECT @Time AS OriginalTime

The result would be:

OriginalTime
------------
17:48:57.2000000

As you can see, SQL Server 2008 provides a more intuitive and efficient way to work with date and time values by offering separate DATE and TIME datatypes.

Reference: Pinal Dave (https://blog.sqlauthority.com)