1. Building dates
Welcome! In this course, we'll look at a number of techniques for working with time series data in SQL Server. I'm Maham, a Senior Content Developer at DataCamp.
2. What you will learn
Throughout this course, we'll use built-in functionality to solve problems with SQL Server.
We'll start by exploring how SQL Server handles individual parts which make up a date. We'll also translate strings, for example, from CSV files, to dates.
We'll take advantage of filtering, grouping and aggregation functions to control date granularity. We'll learn all about downsampling data. For example, instead of having a data point for each second or minute, we can aggregate data over a longer period, such hourly or daily. We'll also learn about upsampling, which involves increasing a time series' frequency with additional data points.
We'll cover specific business cases around windows of data, enabling us to calculate running totals, moving averages, and overlapping date ranges.
3. Building a date
Let's begin with date handling basics. DATETIME and DATETIME2 are two datatypes that represent dates and times in SQL server.
DATETIME is an older data type that supports dates between the year 1753 and 9999, whereas DATETIME2 begins from the year 0001.
The functions shown help us manipulate these dates. GETDATE() returns the current date and time in local time, as a DATETIME object. GETUTCDATE() also returns a date as a DATETIME object, but in UTC.
Similarly, SYSDATETIME() and SYSUTCDATETIME() return the current date and time, either local or UTC, but as a DATETIME2 object.
In both cases, we get the dates and times we expect.
4. Breaking down a date
Suppose we only want a specific part of a date. The built-in functions shown help us get the year, month or day!
5. Parsing dates with date parts
If we want more versatile control while extracting date parts from a date, we can leverage DATEPART() and DATENAME().
DATEPART() takes two arguments, the datepart type, such as YEAR, QUARTER, MONTH, DAYOFYEAR, WEEK etc, and the column or date from which to extract the datepart.
Conversely, DATENAME() takes the same arguments, but returns a string, for instance, if we wanted a month's name.
Several datepart types can be passed to these functions, as shown. We can even get the year's week or year's ISO week, a format mostly used in Europe.
We could go smaller, extracting minutes, seconds, milliseconds, even nanoseconds!
6. Adding and subtracting dates
But parsing dateparts is not the only useful date manipulation we can do. We can also add and subtract dates with DATEADD(). DATEADD() takes three arguments: a datepart, a number to add, and a column name or variable.
Suppose we use a DECLARE statement to create the DATETIME2 variable shown.
We can add and subtract days to this with DATEADD(). We can even chain function calls by changing the first argument for additional precision. In the example shown, the third variable of DATEADD() is another DATEADD() function, which will be evaluated first!
7. Comparing dates
DATEDIFF() allows us to compare units of time between two dates or times. Suppose we have a start and end time for a machine.
DATEDIFF() helps determine seconds, minutes, or hours that have elapsed.
Be warned that DATEDIFF() returns an integer and rounds up.
8. Rounding dates
SQL Server does not have an intuitive way to round down to the month, hour, or minute. To do this, we'll combine DATEADD() and DATEDIFF().
To round down the SomeTime variable shown down to the year, we call DATEADD(YEAR, DATEDIFF(YEAR, 0, SomeTime), 0). To round that down to the month, we call DATEADD(MONTH, DATEDIFF(MONTH, 0, SomeTime), 0). This works for other date parts too.
9. Let's practice!
Let's handle some dateparts!