SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE work 
(
Name NVARCHAR(50), 
CreatedOn DATETIME
);
INSERT INTO work (Name, CreatedOn)
VALUES ('Company A', '2020-10-27 13:49:15.597');
INSERT INTO work (Name, CreatedOn)
VALUES ('Company B', '2021-04-27 15:25:09.720');
INSERT INTO work (Name, CreatedOn)
VALUES ('Company C', '2021-03-16 15:50:01.443');
INSERT INTO work (Name, CreatedOn)
VALUES ('Company D', '2018-04-27 21:58:18.903');
4 rows affected

-- How can I select the first day of a month in SQL?
-- Source: https://stackoverflow.com/a/1520803/12511801

-- First day of the date (6 months back): 
DECLARE @date_start_range DATE = CAST(DATEADD(month, DATEDIFF(month, 0, DATEADD(MONTH, -6, GETDATE())), 0) AS DATE);

-- First day of the current month: 
DECLARE @date_end_range DATE = CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS DATE);

SELECT W.* 
FROM work AS W
WHERE CAST(W.CreatedOn AS DATE) BETWEEN @date_start_range AND @date_end_range;


Name
CreatedOn


Company B
2021-04-27 15:25:09.720

Company C
2021-03-16 15:50:01.443

Name	CreatedOn
Company B	2021-04-27 15:25:09.720
Company C	2021-03-16 15:50:01.443