What Is the Data Type of Date in SQL Server?

What Is the Data Type of Date in SQL Server?

In SQL Server, the data type used to store date values is called DATE. The DATE data type was introduced in SQL Server 2008 and is used to store a date value without a time component. It is commonly used when you need to store and manipulate only the date part of a datetime value.

The DATE data type in SQL Server uses 3 bytes of storage and can represent dates from January 1, 0001, through December 31, 9999.

Working with the DATE Data Type

To work with the DATE data type in SQL Server, you can use various functions and operators to perform different operations on date values. Here are some commonly used ones:

Date Functions:

• GETDATE(): Returns the current system date and time.
• DATEADD(interval, number, date): Adds a specified number of intervals (such as days, months, or years) to a given date.
• DATEDIFF(interval, startdate, enddate): Calculates the difference between two dates based on a specified interval.
• YEAR(date): Extracts the year from a given date.
• MONTH(date): Extracts the month from a given date.
• DAY(date): Extracts the day from a given date.

Date Operators:

• =: Checks if two date values are equal.
• >: Checks if one date value is greater than the other.
• <: Checks if one date value is less than the other.
• >=: Checks if one date value is greater than or equal to the other.
• <=: Checks if one date value is less than or equal to the other.

Examples:

Let’s take a look at some examples to better understand how to work with the DATE data type in SQL Server:

-- Example 1: Get the current system date and time
SELECT GETDATE();

-- Example 2: Add 3 days to a given date
DECLARE @startDate DATE = '2022-01-01';
SELECT DATEADD(DAY, 3, @startDate);

-- Example 3: Calculate the difference between two dates in years
DECLARE @date1 DATE = '2000-01-01';
DECLARE @date2 DATE = '2022-01-01';
SELECT DATEDIFF(YEAR, @date1, @date2);

-- Example 4: Check if one date is greater than another
DECLARE @date1 DATE = '2021-01-01';
DECLARE @date2 DATE = '2022-01-01';
SELECT CASE WHEN @date1 > @date2 THEN 'Date 1 is greater' ELSE 'Date 2 is greater' END;

In conclusion, the DATE data type in SQL Server allows you to store and manipulate only the date part of a datetime value. With its various functions and operators, you can perform different operations on date values efficiently. Understanding the DATE data type and its usage is essential for working with dates in SQL Server.