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.
| date_portion | Format |
|---|---|
| year | yy, yyyy |
| month | mm, m |
| second | ss, s |
| day | dd, d |
| week | wk, ww |
| weekday | dw |
| hour | hh |
| nanosecond | ns |
| dayofyear | dy, y |
| millisecond | ms |
| microsecond | mcs |
| minute | mi, n |
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!