Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Get to know the database
Basic revenue metrics
Summary

Instruction

Very well done! Now, we'll learn how to calculate the beginning of the current period (e.g. the current day, month, year, etc.). This will help us create revenue reports for the current year, month, quarter, etc. Take a look:

SELECT DATEADD(Month, DATEDIFF(Month, 0, GETDATE()), 0) AS CurrentMonthStart;

This expression may look a bit complicated. Let's explain it in detail:

  1. Inside the DATEADD() function, there is a zero as the third argument. This zero is interpreted as 1900-01-01 00:00:00.000, i.e., the beginning of time for SQL Server's datetime type.
  2. GETDATE() returns the current date, and DATEDIFF(month, 0, GETDATE()) returns the number of full months between the beginning of time and the current date.
  3. DATEADD(Month, DATEDIFF(Month, 0, GETDATE()), 0) adds the number of full months returned by DATEDIFF() to the beginning of time. This will provide you with the first day of the current month.

Note that you can replace Month with Day, Quarter, or Year.

Exercise

In a column named CurrentYearStart, show the beginning of the current year.

Stuck? Here's a hint!

Use the code from the explanation. Change Month to Year.