Very well done! Now that we know
DATEADD(), we'll learn how to calculate the beginning of the current day, month, year, etc. This will help us find the registration count for the current time period. 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:
- 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
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.
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