In this SQL Server tutorial, you will learn the DATEPART function in SQL Server. You will understand the use cases of the DATEPART() function and its syntax.
After that, you will learn how to get the specific parts such as day, month, and year from a given date. Then, you will also learn how to extract the time part from the given datetime value.
Finally, you apply the DATEPART() function on the table column to extract the specific part of the date value in that column.
DATEPART Function in SQL Server
DATEPART() function in SQL Server extracts the integer from the given date, and this integer is the datepart, such as the day, month, year, hour, minute, seconds, etc. So, if you have a date/time value and want to extract the specific part of the datetime value, use the DATEPART() function.
What are the use cases of the DATEPART() function? If you have records in your table and want to generate reports based on specific time, such as monthly sales reports.
It also helps analyse the trends or patterns that occur at a particular time of the day, month, etc. You can use the DATEPART() function in different situations or scenarios.
The syntax is given below.
DATEPART ( datepart , date )
Where,
- DATEPART(): This is the function name that returns the specific datepart of the given date.
- datepart: It is the datepart you want to extract from the given date, such as minute, hour, day, month, year etc.
- date: The original date from which you want to extract the date part. It can be an expression such as column, string literal, etc., but make sure it has the following datatype: date, datetime, datetimeoffset, datetime2, smalldatetime, and time.
The return type of the DATEPART() function is int type. Each datepart and its abbreviations return the same value. Now use the following datepart arguments within DATEPART() function:
date_part | meaning |
yy, yyyy, year | This means the year part of the given date. |
qq, q, quarter | It represents the quarter part of the given date. |
mm, m, month | This is the month part of the date. |
dd, d, day | day part of the date. |
dy, y, dayofyear | day of the year part of the date. |
dw, w | weekday of the given date. |
wk, ww, week | This is the week part of the given date. |
mi,n, minute | It is the minute part. |
hh, hour | The hour part. |
ss,s, second | Second part. |
ms, millisecond | milliseconds |
mcs | microseconds |
n | milliseconds |
ISOWK, ISOWW, ISOWEEK | It is the ISO_WEEK. |
tz,tzoffset | It is the time zone offset. |
Now, let’s see how the DATEPART function returns the datepart using the examples, use the below query to extract the year part of the given date ‘2023-1204’.
SELECT DATEPART(year, '2023-12-04') AS YearOfDate;
As you can see in the above output, when you specify datepart as year for the given date ‘2023-12-04’, the DATEPART() function returns 2023 as the year of the specified date. Instead of datepart argument as year, you can use the other abbreviation for the year, such as yy and yyyy.
Next, find the month of the same date using the below query.
SELECT DATEPART(month, '2023-12-04') AS YearOfDate;
Specifying the datepart argument as a month, the DATEPART() function returns the month as 12 for the given date ‘2023-12-04’. Instead of the month, you can use the abbreviations mm and m.
Next, let’s find the day par to the given date using the below query.
When you pass the datepart as day, the DATEPART() function returns the day as the 4 for the given date ‘2023-12-04’.
Here, you must understand that you can extract the specific part of the given date based on the provided datepart argument mentioned in the above table.
This is how to use the DATEPART function in SQL Server to extract the datepart of the specified date.
DATEPART function in SQL Server with Datetime Value
In the previous section, you learned to extract only specific parts such as year, month, and day of the date. Here, you will see how to extract only the timing part of the given datetime or timestamp value that contains both date and time.
For example, use the query below to extract the hour part of the given date and time value.
SELECT DATEPART(hour, '2023-12-04 01:34:45') AS HourOfDateTime;
Look at the time of the given datetime value, which is ’01:34:45′, so when you pass the datepart argument as ‘hour’, then the DATEPART() function returns only the hour as 1 from the given datetime value ‘2023-12-04 01:34:45’.
Also, extract the minute part of the same datetime value using the below query.
SELECT DATEPART(minute, '2023-12-04 01:34:45') AS MinuteOfDateTime;
Passing the datepart argument as a minute to DATEPART() function in the above query returns the minute as 34 from the given timestamp ‘2023-12-04 01:34:45’.
Again, use the same datetime value and extract the second date from that datetime value.
SELECT DATEPART(second, '2023-12-04 01:34:45') AS SecondOfDateTime;
When you execute the above query, the DATEPART() function returns the second as 45 from the specified timestamp ‘2023-12-04 01:34:45’.
This is how to extract the timing part of the given datetime using the DATEPART function in SQL Server.
DATEPART function in SQL Server with Table Column
Until now, you know how to extract the specified datepart of the given date. Here, you will learn about using the DATEPART() function on the table column. You can also provide the column as the expression or date to the DATEPART() function.
For example, suppose you have a Transactions table with columns TransactionID, TransactionDate and Amount, which are shown below.
You work at the bank and must prepare quarterly financial reports, so use the query below.
SELECT DATEPART(quarter, TransactionDate) AS Quarter, SUM(Amount) AS TotalAmount
FROM Transactions
GROUP BY DATEPART(quarter, TransactionDate);
As you can see from the output, the above query computes the total transaction amount for each quarter of the year. For each group (each quarter), it sums up all the transaction amounts that fall into that quarter.
For example, the result set contains two columns: the Quarter, which shows the quarter, and the Total Amount, which shows the total number of transactions for that quarter. The total amount for the 1st quarter is 1726.50, as shown in the above output.
Let’s understand the query part DATEPART(quarter, TransactionDate), which extracts the quarter from the TransactionDate column. As you know, a quarter represents three months.
Next, the query part, SUM(Amount), sums all the values in the Amount column of the Transactions table. Then GROUP BY DATEPART(quarter, TransactionDate) groups the table rows based on the quarter of the ‘TransactionDate’.
This is how to use the DATEPART function in SQL Server with the table’s columns.
Conclusion
In this SQL Server tutorial, you extracted the date part such as day, month, year, hour, minutes and second from the given datetime value using the DATEPART() function in SQL Server. You also extracted the quarter from the table columns’ value using the DATEPART() function in SQL Server.
You may like to read:
- DATENAME Function of SQL Server
- SYSUTCDATETIME Function in SQL Server
- How to use SYSDATETIME Function in SQL Server
- DIFFERENCE Function in SQL Server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.