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”
Exercise instructions
- Use
DATEADD()andDATEDIFF()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
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 datesExercise 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 tableChapter 2: Converting to Dates and Times
Here, we'll be converting strings and other inputs to date and time data types.
Exercise 1: Building dates from partsExercise 2: Build dates from partsExercise 3: Build dates and times from partsExercise 4: Build dates and times with offsets from partsExercise 5: Translating date stringsExercise 6: Cast strings to datesExercise 7: Convert strings to datesExercise 8: Parse strings to datesExercise 9: Working with offsetsExercise 10: Changing a date's offsetExercise 11: Using the time zone DMV to look up timesExercise 12: Converting to a date offsetExercise 13: Handling invalid datesExercise 14: Try out type-safe date functionsExercise 15: Convert imported data to dates with time zonesExercise 16: Test type-safe conversion function performanceChapter 3: Aggregating Time Series Data
In this chapter, we will learn techniques to aggregate data over time. We will briefly review aggregation functions and statistical aggregation functions. We will cover upsampling and downsampling of data. Finally, we will look at the grouping operators.
Exercise 1: Basic aggregate functionsExercise 2: Summarize data over a time frameExercise 3: Calculating distinct countsExercise 4: Calculating filtered aggregatesExercise 5: Statistical aggregate functionsExercise 6: Working with statistical aggregate functionsExercise 7: Calculating median in SQL ServerExercise 8: Downsampling and upsampling dataExercise 9: Downsample to a daily grainExercise 10: Downsample to a weekly grainExercise 11: Downsample using a calendar tableExercise 12: Grouping by ROLLUP, CUBE, and GROUPING SETSExercise 13: Generate a summary with ROLLUPExercise 14: View all aggregations with CUBEExercise 15: Generate custom groupings with GROUPING SETSExercise 16: Combine multiple aggregations in one queryChapter 4: Answering Time Series Questions with Window Functions
In this chapter, we will learn how to use window functions to perform calculations over time, including calculating running totals and moving averages, calculating intervals, and finding the maximum levels of overlap.
Exercise 1: Using aggregation functions over windowsExercise 2: Contrasting ROW_NUMBER(), RANK(), and DENSE_RANK()Exercise 3: Aggregate window functionsExercise 4: Calculating running totals and moving averagesExercise 5: Running totals with SUM()Exercise 6: Investigating window framesExercise 7: Calculating moving averagesExercise 8: Working with LAG() and LEAD()Exercise 9: Seeing prior and future periodsExercise 10: Seeing the prior three periodsExercise 11: Calculating days elapsed between incidentsExercise 12: Finding maximum levels of overlapExercise 13: Analyze client data for potential fraudExercise 14: Build a stream of eventsExercise 15: Complete the fraud analysisExercise 16: Wrapping upWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.