get the first day of the month-CodePudding

I initially started with getting few months back from current date

SELECT DATEADD(month, -3, getdate())

which gives me the date range from three months ago to today, as of today 9/26/2022, the code gave me 6/26/2022. I would like to get up to the first day of June which is 6/1/2022.

However, the following functions according to some googling returned error:

DATE_TRUNC(month,DATEADD(month, -3, getdate()))

DATETRUNC(month,DATEADD(month, -3, getdate()))

from https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16

or the trunc_date.

CodePudding user response：

Assuming you are resolving this only once and not on numerous records.

Example

SELECT DATEADD(month, -3, format(getdate(),'yyyy-MM-01'))

Results

2022-06-01 00:00:00.000

CodePudding user response：

What error are you getting? Did you see that DATETRUNC...

Applies to: SQL Server 2022 (16.x) Preview

Meaning if you aren't running on a very recent preview build of SQL Server 2022, it's not going to work, and this is the error you should be seeing:

Msg 195, Level 15, State 10
'DATETRUNC' is not a recognized built-in function name.

Another way is to use EOMONTH() to get the first day of next month, then subtract 4 months instead of 3:

SELECT DATEADD(MONTH, -4, DATEADD(DAY, 1, EOMONTH(GETDATE())));

Or DATEFROMPARTS(), which is simpler (especially if you perform part of the calculation ahead of time):

DECLARE @d date = DATEADD(MONTH, -3, GETDATE());

SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);