The DATEPART function is commonly used in SQL (particularly in SQL Server) to extract specific components of a date or time value. This function allows you to retrieve parts such as year, month, day, hour, minute, second, and more based on the specified datepart.
Syntax
DATEPART(datepart, date)
- datepart: This is the part of the date you want to extract, such as YEAR, MONTH, DAY, HOUR, etc.
- date: This is the date or timestamp from which you want to extract the part.
Common Datepart Values
Here are some common datepart values you can use:
- year or yy: Extracts the year.
- month or mm: Extracts the month.
- day or dd: Extracts the day of the month.
- hour or hh: Extracts the hour.
- minute or mi: Extracts the minute.
- second or ss: Extracts the second.
- quarter or qq: Extracts the quarter of the year.
Example Usage
Here are some examples demonstrating how to use the DATEPART function:
1. Extract the Year
SELECT DATEPART(year, '2023-10-15') AS YearPart;
Output:
YearPart
--------
2023
2. Extract the Month
SELECT DATEPART(month, '2023-10-15') AS MonthPart;
Output:
MonthPart
---------
10
3. Extract the Day
SELECT DATEPART(day, '2023-10-15') AS DayPart;
Output:
DayPart
-------
15
4. Extract the Hour
SELECT DATEPART(hour, '2023-10-15 14:30:00') AS HourPart;
Output:
HourPart
--------
14
5. Extract the Minute
SELECT DATEPART(minute, '2023-10-15 14:30:00') AS MinutePart;
Output:
MinutePart
----------
30
Practical Use Cases
- You might use DATEPART in a WHERE clause to filter records according to a specific year or month.
- It's also useful for grouping records by a specific date part in aggregate functions.
Conclusion
The DATEPART function is a powerful tool in SQL for manipulating and analyzing date and time data. By extracting specific components from date values, you can perform a wide range of data analysis and reporting tasks.