Hello Dev! Are you looking for ways to efficiently work with dates in SQL Server? Then you have come to the right place. In this article, we will explore various techniques to work with dates in SQL Server effectively. By the end of this article, you will have a deep understanding of how to manipulate dates and times in SQL Server. So, let’s get started!
What is SQL Server Date Data Type?
Before we dive into the topic, let’s first understand what date data type is in SQL Server. The date data type is used to store date and time values in SQL Server. The date data type was first introduced in SQL Server 2008. It can store values from January 1, 1753, to December 31, 9999. The date data type has a minimum accuracy of 100 nanoseconds.
When you store a value in the date data type, it gets stored in a binary format. The binary format takes up 3 bytes of storage. When you retrieve the value from the date data type, SQL Server converts the binary format back to a human-readable format.
Now that we have understood what the date data type is in SQL Server, let’s move on to our next topic.
How to Get Today’s Date in SQL Server?
The most common requirement is to get today’s date in SQL Server. You can easily get today’s date in SQL Server using the GETDATE() function. The GETDATE() function returns the current date and time in SQL Server.
The syntax for using the GETDATE() function is:
Syntax |
Output |
|---|---|
SELECT GETDATE() |
2021-07-22 16:10:28.440 |
As you can see in the above example, the GETDATE() function returns the current date and time. However, if you only want to get today’s date, you can use the CAST() function to cast the datetime value to a date value.
The syntax for getting today’s date is:
Syntax |
Output |
|---|---|
SELECT CAST(GETDATE() AS DATE) |
2021-07-22 |
Now that you know how to get today’s date in SQL Server, let’s move on to our next topic.
How to Get Yesterday’s Date in SQL Server?
If you want to get yesterday’s date in SQL Server, you can subtract 1 from today’s date. However, you cannot directly subtract a number from a date data type. You need to cast the date data type to datetime data type and then perform the subtraction.
The syntax for getting yesterday’s date is:
Syntax |
Output |
|---|---|
SELECT CAST(DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) AS DATE) |
2021-07-21 |
The DATEADD() function adds or subtracts a specified number of intervals (days, months, years or seconds) to a date. In the above syntax, we are subtracting 1 day from today’s date using the DATEADD() function. Then we are casting the result to a date data type.
Now that we have discussed how to get yesterday’s date in SQL Server, let’s move on to our next topic.
How to Get Tomorrow’s Date in SQL Server?
If you want to get tomorrow’s date in SQL Server, you can add 1 to today’s date. Similar to getting yesterday’s date, you need to cast the date data type to datetime data type and then perform addition.
The syntax for getting tomorrow’s date is:
Syntax |
Output |
|---|---|
SELECT CAST(DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) AS DATE) |
2021-07-23 |
Now that you know how to get tomorrow’s date in SQL Server, let’s move on to our next topic.
How to Get Current Year in SQL Server?
If you want to get the current year in SQL Server, you can use the YEAR() function. The YEAR() function returns the year part of a date.
The syntax for getting the current year is:
Syntax |
Output |
|---|---|
SELECT YEAR(GETDATE()) |
2021 |
The YEAR() function returns the year part of the current date and time. If you want to get the year part of a specific date, you can replace GETDATE() with the date you want to get the year for.
Now that you know how to get the current year in SQL Server, let’s move on to our next topic.
How to Get Current Month in SQL Server?
If you want to get the current month in SQL Server, you can use the MONTH() function. The MONTH() function returns the month part of a date.
The syntax for getting the current month is:
Syntax |
Output |
|---|---|
SELECT MONTH(GETDATE()) |
7 |
The MONTH() function returns the month part of the current date and time. If you want to get the month part of a specific date, you can replace GETDATE() with the date you want to get the month for.
Now that you know how to get the current month in SQL Server, let’s move on to our next topic.
How to Get Current Day in SQL Server?
If you want to get the current day in SQL Server, you can use the DAY() function. The DAY() function returns the day part of a date.
The syntax for getting the current day is:
Syntax |
Output |
|---|---|
SELECT DAY(GETDATE()) |
22 |
The DAY() function returns the day part of the current date and time. If you want to get the day part of a specific date, you can replace GETDATE() with the date you want to get the day for.
Now that you know how to get the current day in SQL Server, let’s move on to our next topic.
How to Get Last Day of the Month in SQL Server?
If you want to get the last day of the month in SQL Server, you can use the EOMONTH() function. The EOMONTH() function returns the last day of the month for a given date.
The syntax for getting the last day of the month is:
Syntax |
Output |
|---|---|
SELECT EOMONTH(GETDATE()) |
2021-07-31 |
In the above example, the EOMONTH() function returns the last day of the current month. If you want to get the last day of the month for a specific date, you can replace GETDATE() with the date you want to get the last day for.
Now that you know how to get the last day of the month in SQL Server, let’s move on to our next topic.
How to Get First Day of the Month in SQL Server?
If you want to get the first day of the month in SQL Server, you can use the DATEFROMPARTS() function. The DATEFROMPARTS() function returns a date value for a given year, month, and day.
The syntax for getting the first day of the month is:
Syntax |
Output |
|---|---|
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) |
2021-07-01 |
The DATEFROMPARTS() function returns a date value for the year, month, and day specified in the function. In the above example, we are getting the year and month from the current date using the YEAR() and MONTH() functions, respectively. Then we are specifying 1 as the day to get the first day of the month.
Now that you know how to get the first day of the month in SQL Server, let’s move on to our next topic.
How to Compare Dates in SQL Server?
If you want to compare dates in SQL Server, you can use the comparison operators (=, <, >, <=, >=, <>) with the date data type. SQL Server compares dates in the format YYYY-MM-DD.
The syntax for comparing two dates is:
Syntax |
Output |
|---|---|
SELECT ‘2021-07-22’ > ‘2021-07-21’ |
1 |
In the above example, we are comparing two dates using the greater than operator (>). The comparison operator returns 1 if the condition is true and 0 if the condition is false.
Now that you know how to compare dates in SQL Server, let’s move on to our next topic.
How to Add Days to a Date in SQL Server?
If you want to add days to a date in SQL Server, you can use the DATEADD() function. The DATEADD() function adds a specified number of intervals (days, months, years or seconds) to a date.
The syntax for adding days to a date is:
Syntax |
Output |
|---|---|
SELECT DATEADD(DAY, 5, ‘2021-07-22’) |
2021-07-27 |
In the above example, we are adding 5 days to the date ‘2021-07-22’ using the DATEADD() function. The first parameter of the DATEADD() function is the interval you want to add. In this case, we are adding days. The second parameter is the number of intervals you want to add. The third parameter is the date you want to add the intervals to.
Now that you know how to add days to a date in SQL Server, let’s move on to our next topic.
How to Subtract Days from a Date in SQL Server?
If you want to subtract days from a date in SQL Server, you can use the DATEADD() function with a negative number of intervals. The DATEADD() function adds a specified number of intervals (days, months, years or seconds) to a date.
The syntax for subtracting days from a date is:
Syntax |
Output |
|---|---|
SELECT DATEADD(DAY, -5, ‘2021-07-22’) |
2021-07-17 |
In the above example, we are subtracting 5 days from the date ‘2021-07-22’ using the DATEADD() function. The first parameter of the DATEADD() function is the interval you want to add. In this case, we are subtracting days by passing a negative value. The second parameter is the number of intervals you want to add. The third parameter is the date you want to add the intervals to.
Now that you know how to subtract days from a date in SQL Server, let’s move on to our next topic.
How to Get Date Difference in Days in SQL Server?
If you want to get the date difference in days in SQL Server, you can subtract one date from another using the DATEDIFF() function. The DATEDIFF() function returns the difference between two dates in the specified interval.
The syntax for getting date difference in days is:
Syntax |
Output |
|---|---|
SELECT DATEDIFF(DAY, ‘2021-07-17’, ‘2021-07-22’) |
5 |
In the above example, we are getting the date difference in days between the dates ‘2021-07-17’ and ‘2021-07-22’ using the DATEDIFF() function. The first parameter of the DATEDIFF() function is the interval you want to get the difference in. In this case, we are getting the difference in days. The second and third parameters are the two dates you want to get the difference between.
Now that you know how to get date difference in days in SQL Server, let’s move on to our next topic.
How to Get Date Difference in Months in SQL Server?
If you want to get the date difference in months in SQL Server, you can use the DATEDIFF() function with the MONTH interval. The DATEDIFF() function returns the difference between two dates in the specified interval.
The syntax for getting date difference in months is:
Syntax |
Output |
|---|---|
SELECT DATEDIFF(MONTH, ‘2021-07-01’, ‘2021-09-01’) |
2 |
In the above example, we are getting the date difference in months between the dates ‘2021-07-01’ and ‘2021-09-01’ using the DATEDIFF() function with the MONTH interval.
Now that you know how to get date difference in months in SQL Server, let’s move on to our next topic.
How to Get Date Difference in Years in SQL Server?
If you want to get the date difference in years in SQL Server, you can use the DATEDIFF() function with the YEAR interval. The DATEDIFF() function returns the difference between two dates in the specified interval.
The syntax for getting date difference in years is:
Syntax |
Output |
|---|---|
SELECT DATEDIFF(YEAR, ‘2010-01-01’, ‘2021-01-01’) |
11 |
In the above example, we are getting the date difference in years between the dates ‘2010-01-01’ and ‘2021-01-01’ using the DATEDIFF() function with the YEAR interval.
Now that you know how to get date difference in years in SQL Server, let’s move on to our next topic.
FAQs
Q) Can I store date and time values in SQL Server?
A) Yes, you can store date and time values in SQL Server using the datetime or datetime2 data type.
Q) What is the difference between datetime and datetime2 data types?
A) The datetime data type can store dates from January 1, 1753, to December 31, 9999, and has an accuracy of 3.33 milliseconds. The datetime2 data type was introduced in SQL Server 2008 and can store dates from January 1,