The SQL DATEPART function returns the specified part of the input date.
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.
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.
/* Answer */ SELECT Description, StartDate, DATEPART(Year,StartDate) StartYear, DATEPART(QUARTER,StartDate) StartQuarter, DATEPART(MONTH,StartDate) StartMonth, DATEPART(WEEK,StartDate) StartWeek, DATEPART(DAY,StartDate) StartDay FROM Sales.SpecialOffer ORDER BY 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.
/* Answer */ SELECT * FROM Sales.SpecialOfferProduct SOP INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID WHERE DATEPART(MONTH, StartDate) = 7
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.
/* Answer */ SELECT PurchaseOrderID,DueDate FROM Purchasing.PurchaseOrderDetail WHERE DATEPART(WEEKDAY,DueDate) = 5
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.
/* Answer */ SELECT SalesOrderID,OrderDate FROM Sales.SalesOrderHeader WHERE DATEPART(WEEK,OrderDate) = 1
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.
/* Answer */ SELECT DATEPART (TZoffset, '2021-06-16 00:00:02.1444559 +05:00') as OffsetMinutes
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.
/* Answer */ SELECT DATEPART(year, '10:09:35.373') DefaultYearExample;
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.
- 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.