Extracting Date and Time Components in SQL Server

SQL Server provides powerful functions for manipulating date and time data. Often, you’ll need to extract specific components – like the hour, minute, or day – from a datetime or datetime2 value. This tutorial explains how to achieve this using the DATEPART function.

Understanding DATEPART

The DATEPART function allows you to retrieve a specific part of a date or time value. It takes two arguments:

1. datepart: A string or integer specifying the date or time component you want to extract. Common values include:

   • year or yy or yyyy
   • quarter or qq or q
   • month or mm or m
   • dayofyear or dy or y
   • day or dd or d
   • week or wk or ww
   • weekday or dw or w
   • hour or hh
   • minute or mi or n
   • second or ss or s
   • millisecond or ms
   • microsecond or mcs
   • nanosecond or ns

2. date: The datetime or datetime2 value from which you want to extract the component. This can be a column name, a variable, or a literal date/time value.

Examples

Let’s illustrate how to use DATEPART with different components.

• Extracting the Hour:

  SELECT DATEPART(hour, GETDATE());
  

  This query retrieves the current hour (0-23) from the system’s current date and time. GETDATE() returns the current datetime.

• Extracting the Month:

  SELECT DATEPART(month, '2024-10-26');
  

  This query will return 10, representing the month of October.

• Extracting the Day:

  SELECT DATEPART(day, '2024-10-26');
  

  This query will return 26, representing the day of the month.

• Using DATEPART in a WHERE clause:

  SELECT *
  FROM Orders
  WHERE DATEPART(hour, OrderDate) = 10;
  

  This query selects all orders placed at 10:00 AM.

• Extracting multiple components:

  You can extract multiple components in a single query:

  SELECT
      DATEPART(year, OrderDate) AS OrderYear,
      DATEPART(month, OrderDate) AS OrderMonth,
      DATEPART(day, OrderDate) AS OrderDay
  FROM Orders;
  

Important Considerations

• DATEPART returns an integer representing the extracted component.
• The datepart argument is case-insensitive. hour and HOUR are equivalent.
• DATEPART works with both datetime and datetime2 data types.
• When using DATEPART in a WHERE clause, consider indexing the column you are filtering on to improve query performance.