SQL Interview Questions

The DATEPART and DATEDIFF functions in SQL are used to manipulate and extract parts of date values and to calculate the difference between dates, respectively. They are commonly used in date and time calculations and formatting.

DATEPART Function

The DATEPART function extracts a specific part of a date, such as the year, month, day, hour, minute, etc. It returns an integer representing the specified part of the date.

Syntax

DATEPART(part, date)

• part: The part of the date to extract. Common values include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, etc.
• date: The date from which to extract the specified part.

Example Usage

1. Extract Year:

SELECT DATEPART(year, '2023-07-30') AS Year;

Result:

Year
2023

2. Extract Month:

SELECT DATEPART(month, '2023-07-30') AS Month;

Result:

Month
7

3. Extract Day:

SELECT DATEPART(day, '2023-07-30') AS Day;

Result:

Day
30

4. Extract Hour:

SELECT DATEPART(hour, '2023-07-30 15:45:00') AS Hour;

Result:

Hour
15

DATEDIFF Function

The DATEDIFF function calculates the difference between two dates, returning the result as an integer. The difference can be calculated in various units such as year, month, day, hour, minute, second, etc.

Syntax

DATEDIFF(part, start_date, end_date)

• part: The unit of time to use for the difference calculation. Common values include year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, etc.
• start_date: The starting date for the calculation.
• end_date: The ending date for the calculation.

Example Usage

1. Difference in Days:

SELECT DATEDIFF(day, '2023-01-01', '2023-07-30') AS DifferenceInDays;

Result:

DifferenceInDays
210

2. Difference in Months:

SELECT DATEDIFF(month, '2023-01-01', '2023-07-30') AS DifferenceInMonths;

Result:

DifferenceInMonths
6

3. Difference in Years:

SELECT DATEDIFF(year, '2020-01-01', '2023-07-30') AS DifferenceInYears;

Result:

DifferenceInYears
3

4. Difference in Hours:

SELECT DATEDIFF(hour, '2023-07-30 00:00:00', '2023-07-30 15:45:00') AS DifferenceInHours;

Result:

DifferenceInHours
15

Practical Use Cases

Example 1: Calculate Age

To calculate the age of a person based on their birth date:

SELECT DATEDIFF(year, '1990-05-15', GETDATE()) AS Age;

This query calculates the number of years between the birth date and the current date.

Example 2: Extract Parts of a Date for Grouping

To group sales data by month and year:

SELECT 
    DATEPART(year, sale_date) AS SaleYear,
    DATEPART(month, sale_date) AS SaleMonth,
    SUM(sale_amount) AS TotalSales
FROM sales
GROUP BY 
    DATEPART(year, sale_date),
    DATEPART(month, sale_date);

Example 3: Calculate Time Duration

To calculate the duration of an event in hours:

SELECT 
    event_id,
    DATEDIFF(hour, start_time, end_time) AS DurationInHours
FROM events;

Conclusion

The DATEPART and DATEDIFF functions in SQL are essential tools for date and time manipulation. DATEPART allows you to extract specific parts of a date, while DATEDIFF enables you to calculate the difference between two dates in various units. Understanding and utilizing these functions can significantly enhance your ability to handle date and time data in SQL queries, making them invaluable for data analysis and reporting.