Below are three options for using T-SQL to return the first day of the month in SQL Server.

This could be the first day of the current month, or the first day of a month based on a given date.

Option 1

One way to do it is like this:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT DATEADD(dd, -( DAY( @date ) -1 ), @date);

Result:

2035-10-01

This involves using some T-SQL functions to perform date shifting in order to get the date back to the start of the month.

For more information about the DATEADD() and DAY() functions, see DATEADD() Examples in SQL Server and DAY() Examples in SQL Server.

Option 2

Here’s another option for getting the first day of the month:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

Result:

2035-10-01 00:00:00.000

Here, we incorporated the DATEDIFF() function into our calculation.

Although we declared the initial variable as a date value, the result is a datetime value. We can use CONVERT() or CAST() to convert the result to a date value:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT CAST(DATEADD(month, DATEDIFF(month, 0, @date), 0) AS date);

Result:

2035-10-01

Option 3

Here’s another option:

DECLARE @date datetime;
SET @date = '2035-10-15';
SELECT @date - DAY( @date ) + 1;

Result:

2035-10-01 00:00:00.000

Similar to option 2, the result is a datetime value, but this time it’s because we declared the variable as a datetime value. However, we can give it the same treatment to convert it to a date value:

DECLARE @date datetime;
SET @date = '2035-10-15';
SELECT CAST(@date - DAY( @date ) + 1 AS date);

Result:

2035-10-01

The reason we didn’t declare the variable as a date value is because that would result in an error:

DECLARE @date date;
SET @date = '2035-10-15';
SELECT @date - DAY( @date ) + 1;

Result:

Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

This is because we’re trying to add an integer to a date value, which doesn’t work. However, adding an integer to a datetime value does work, and that’s why we declared the variable as datetime.