Get Started

# Rounding dates

SQL Server does not have an intuitive way to round down to the month, hour, or minute. You can, however, combine the `DATEADD()` and `DATEDIFF()` functions to perform this rounding.

To round the date 1914-08-16 down to the year, we would call `DATEADD(YEAR, DATEDIFF(YEAR, 0, '1914-08-16'), 0)`.

To round the date down to the month, we would call `DATEADD(MONTH, DATEDIFF(MONTH, 0, '1914-08-16'), 0)`.

This works for several other date parts as well.

This is a part of the course

## “Time Series Analysis in SQL Server”

View Course

### Exercise instructions

• Use `DATEADD()` and `DATEDIFF()` in conjunction with date parts to round down our time to the day, hour, and minute.

### Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

``````DECLARE
@SomeTime DATETIME2(7) = '2018-06-14 16:29:36.2248991';

-- Fill in the appropriate functions and date parts
SELECT
DATEADD(___, DATEDIFF(DAY, 0, @SomeTime), 0) AS RoundedToDay,
___(___, ___(___, 0, @SomeTime), 0) AS RoundedToHour,
___(___, ___(___, 0, @SomeTime), 0) AS RoundedToMinute;``````

This exercise is part of the course

## Time Series Analysis in SQL Server

IntermediateSkill Level
3.7+
16 reviews

Explore ways to work with date and time data in SQL Server for time series analysis

## Chapter 1: Working with Dates and Times

This chapter covers date and time functionality in SQL Server, including building dates from component parts, formatting dates for reporting, and working with calendar tables.

Exercise 1: Building datesExercise 2: Break out a date into year, month, and dayExercise 3: Break a date and time into component partsExercise 4: Date math and leap yearsExercise 5: Rounding dates
Exercise 6: Formatting dates for reportingExercise 7: Formatting dates with CAST() and CONVERT()Exercise 8: Formatting dates with FORMAT()Exercise 9: Working with calendar tablesExercise 10: The benefits of calendar tablesExercise 11: Try out a calendar tableExercise 12: Joining to a calendar table

## Chapter 4: Answering Time Series Questions with Window Functions

### What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.