DATEPART Function in SQL Server

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_partmeaning
yy, yyyy, yearThis means the year part of the given date.
qq, q, quarterIt represents the quarter part of the given date.
mm, m, monthThis is the month part of the date.
dd, d, dayday part of the date.
dy, y, dayofyearday of the year part of the date.
dw, wweekday of the given date.
wk, ww, weekThis is the week part of the given date.
mi,n, minuteIt is the minute part.
hh, hourThe hour part.
ss,s, secondSecond part.
ms, millisecondmilliseconds
mcsmicroseconds
nmilliseconds
ISOWK, ISOWW, ISOWEEKIt is the ISO_WEEK.
tz,tzoffsetIt 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;
DATEPART function in SQL Server Extracting Year Part of the Date

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;
DATEPART Function in SQL Server Extracting Month Part of the Date

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.

DATEPART Function in SQL Server Extracting Day Part of the Date

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;
DATEPART function in SQL Server with Datetime Value Extracting Hour Part of the DateTime

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;
DATEPART function in SQL Server with Datetime Value Extracting Minute Part of the DateTime

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;
DATEPART function in SQL Server with Datetime Value Extracting Second Part of the DateTime

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.

DATEPART function in SQL Server with Transactions Table Column

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);
DATEPART function in SQL Server with Table Column

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: