Extract only dates for first Day of Month from a daily Date Dimension Table without nesting

I am using SQL Server 2014 and I have the following T-SQL query which retrieves the dates of the first day of each month (based on my filters) from a Date Dimension Table:

SELECT * FROM (
    SELECT DATEADD(month, DATEDIFF(month, 0, [date]), 0) AS [Date2]
    FROM DateDimension
    WHERE [date] BETWEEN '2018-07-01' AND '2019-06-01'
) x
GROUP BY x.[Date2]

The output is as follows:

Date2
2018-07-01 00:00:00.000
2018-08-01 00:00:00.000
2018-09-01 00:00:00.000
2018-10-01 00:00:00.000
2018-11-01 00:00:00.000
2018-12-01 00:00:00.000
2019-01-01 00:00:00.000
2019-02-01 00:00:00.000
2019-03-01 00:00:00.000
2019-04-01 00:00:00.000
2019-05-01 00:00:00.000
2019-06-01 00:00:00.000

I have used a nested query to achieve this. My question is whether I can achieve the same result without using the nested query. My Date Dimension table is a table with daily dates.

Answer

Just repeat the date calculation part inside GROUP BY:

SELECT DATEADD(month, DATEDIFF(month, 0, [date]), 0) AS [Date2]
FROM DateDimension
WHERE [date] between '2018-07-01' and '2019-06-01'
GROUP BY DATEADD(month, DATEDIFF(month, 0, [date]), 0)

PS: this:

DATEADD(month, DATEDIFF(month, 0, [date]), 0)

could be written as:

DATEADD(DAY, 1, EOMONTH([date], -1))

Advertisement

Answer