What is SQL Server DATEPART() method?

Hey, folks! In this article, we will be focusing on SQL Server DATEPART() function.

Working of SQL DATEPART() method

SQL DATEPART() method helps to extract the portion from the standard timestamp structure which is,

yyyy-mm-dd hours-mins-sec

The DATEPART() method extracts and helps in representing the date portions of the timestamp in the form of years, months, days, weeks, hours, mins, etc.

Let us understand the format for each date portion that can be extracted from the date through the below table.

Syntax of SQL DATEPART() method

Having understood the working of SQL DATEPART() method, let us now look at the implementation of the mentioned function.

DATEPART(date_portion, date)

As seen above, the DATEPART() function accepts the date_portion that is the portion to extract out of the date and the input timestamp. The function returns the extracted date portion from the

Examples of SQL DATEPART() method

Now, let us understand the working behind the SQL Server DATEPART() function with the help of various examples stated below.

SQL DATEPART() method with ‘year’ as an interval

In this example, we have passed ‘year’ as the date_portion to be extracted from the input date passed to the function.

Example:

SELECT DATEPART(year, '2020/06/03 12:15:45') AS Output;

Output:

2020

SQL Server DATEPART() method with ‘second’ as an interval

In the below example, the DATEPART() function is used to extract and return the ‘seconds’ value from the input “date” passed to it.

Example:

SELECT DATEPART(second, '2020/06/03 12:15:45') AS Output;

Output:

45

SQL DATEPART() method with ‘minute’ as an interval

Here, the minute value of the date passed is returned by the function.

SELECT DATEPART(minute, '2020/06/03 12:15:45') AS Output;

Output:

15

DATEPART() method with ‘hour’ as an interval

If the time related information is passed along the date format to the function, we can extract the information about time such as hours, minutes, seconds, etc using the DATEPART() function.

Example:

SELECT DATEPART(hour, '2020/06/03 12:15:45') AS Output;

Output:

12

SQL Server DATEPART() method with ‘dayofyear’ as an interval

By passing ‘dayofyear’ to the function, we actually try to extract and estimate the day number from the passed year in the function.

Example:

SELECT DATEPART(dayofyear, '2020/06/03 12:15:45') AS Output;

Output:

155

DATEPART() method with ‘week’ as an interval

Example:

SELECT DATEPART(week, '2020/06/03 12:15:45') AS Output;

Output:

23

Conclusion

By this, we have come to the end of this topic. Please feel free to comment below in case you come across any doubts/questions.

Further, to gain access to such articles related to SQL Server, please do visit SQL Server JournalDev. Also, we’ve covered some more date functions in SQL like DATEADD() and DATEDIFF() which will be handy for you!

References

• SQL Server DATEPART() method — Documentation