The SQL DATEPART function returns the specified part of the input date.
Description
The SQL DATEPART function returns an integer value that indicates the part of the date specified by the user. The interval to be retrieved can be a date, year, hour, minute, etc.
Returns an integer which represents the specified part of a date. It works very similar to DATENAME, in that you specify a date part and value, however, rather than returning a text value, such as ‘July’, it returns 7.
The general form of DATEPART is
DATEPART(date part, value)
Where date part corresponds to a list of predefined parts and the value is the date you are working with.
This function returns an integer.
Here is an example which shows various date parts in action.
SELECT DATEPART(YEAR, GETDATE()) as Year, DATEPART(WEEK, GETDATE()) as Week, DATEPART(DAYOFYEAR, GETDATE()) as DayOfYear, DATEPART(MONTH, GETDATE()) as Month, DATEPART(DAY, GETDATE()) as Day, DATEPART(WEEKDAY, GETDATE()) as WEEKDAY
Assuming GETDATE returns 2015-08-10 12:56:25.313 the example query results are
Note: There are many other date parts you can use with DATEPART. You can find a complete list on the MSDN site.
We can also get a summary of sales totals using DATEPART as we did for DATENAME. Here is the query
SELECT DATEPART(year, OrderDate) as OrderYear, DATEPART(week, OrderDate) as OrderWeek, SUM(TotalDue) as WeeklySales FROM Sales.SalesOrderHeader WHERE DATEPART(year, OrderDate) = 2008 GROUP BY DATEPART(year, OrderDate), DATEPART(week, OrderDate) ORDER BY DATEPART(year, OrderDate), DATEPART(week, OrderDate)
You notice that these results are sorted correctly according to week:
Syntax
DATEPART (date_part, input_date)
SQL DATEPART Usage Notes
The first argument for the DATEPART function is the date part, for which the function will return an integer value.
- The valid values for the date part argument are year, quarter, month, day, week, hour, minute, second, etc. The date part does not accept the user-defined values as a valid date part argument.
- For the week and weekday, the DATEPART returns the value according to the week’s first day as set by the DATEFIRST function. DATEFIRST function sets the first day of the week through numbering from 1 to 7. 7 sets Sunday as the week’s first day. It is also the default value per US English.
- If the date is of smalldatetime data type, then DATEPART returns 00 for seconds.
- The second argument is the date from which the DATEPART extracts the specified component of the date. The data type can be a date, datetime, datetimeoffset, datetime2, smalldatetime, and time. Moreover, user-defined variables and column expressions are also valid values for the date argument.
- If the specified date interval is not present in the input date, then the DATEPART returns the default value. For example, 1900-01-01 is the default year, month, and day for a date value with no specified year, month, and day, like, ’10:10:40.33′.
- The return type of the DATEPART function is an integer. All the date parts and their abbreviations return the same integer value. For example, year, yyyy, yy will have the same return value.
SQL DATEPART Examples
The following SQL queries use the AdventureWorks2019 database.
The following example show how you can get common date parts from the Sales.SpecialOffer StartDate.
Notice we use YEAR, QUARTER, MONTH, WEEK, and DAY parts within the query. Hopefully, this helps you see how versatile DATERPART is for you to use.
Suppose we want to list all the products that are on offer in July. The following query will work.
The Inner Join joins two tables, SpecialOfferProduct and SpecialOffer by the SpecialOfferId. We specify the month value as a DATEPART argument in the where clause. We specify 7 as July is the seventh month in the year.
Suppose the DATEFIRST function sets the week’s first day to Monday.
SET DATEFIRST 7
Now, the following query displays purchase orders that are expected only on Fridays.
If we change the first day of the week, the above query will show different results.
The following query lists all the orders from the SalesOrderHeader table that lie in the first week of every month.
If we change the week data part value to ISO_WEEK, we will get different results. The weeks then number according to the ISO 8601 numbering system.
The following examples show the working of the tzoffset date part argument.
The query returns the number of minutes between the given local time and the provided offset. The offset here is of plus 5 hours.
As discussed above, the following query will return 1990 as the default year.
As the input date does not contain the required date part that is a year, the DATEPART function returns the default value for the year.
Additional Notes!
- Another point to keep in mind when using the week or weekday is iso_week date part. According to ISO 8601 week-date system, if a week contains Thursday, then it forms a week of the year. Sweden and Norway follow this week rule but not all other regions of the world. Hence, the DATEPART function may return different values for the weekday or the week according to the week numbering system. For the European region, the week-date system counts Sunday as the first day of the week, whereas in the US, a week starts from Saturday. According to three other weekday possibilities, a week forms the first week of the year if it contains the first Monday, first Tuesday, or first Friday. The starting days of the week are then Monday, Wednesday, and Friday, respectively.
- The tzoffset argument value returns the time zone offset between the local time zone and the GMT as the number of minutes. If the date data type is datetimeoffset, then the DATEPART returns the time zone offset as minutes. For datetime2, 0 returns. All other data types that are also not implicitly convertible to datetimeoffset or datetime2 return an error.
