Rounding dates to first day of the month

Question

I am using SQL Server 2014 and I am working with a column from one of my tables, which list arrival dates.

It is in the following format:

ArrivalDate
2015-10-17 00:00:00.000
2015-12-03 00:00:00.000

I am writing a query that would pull data from the above table, including the ArrivalDate column. However, I will need to convert the dates so that they become the first day of their respective months.

In other words, my query should output the above example as follows:

2015-10-01 00:00:00.000
2015-12-01 00:00:00.000

I need this so that I can create a relationship with my Date Table in my PowerPivot model.

I've tried this syntax but it is not meeting my requirements:

CONVERT(CHAR(4),[ArrivalDate], 100) + CONVERT(CHAR(4), [ArrivalDate], 120) AS [MTH2]

Answer 1

If, for example, it is 15th of given month then you subtract 14 and cast the result to date:

SELECT ArrivalDate
     , CAST(DATEADD(DAY, -DATEPART(DAY, ArrivalDate) + 1, ArrivalDate) AS DATE) AS FirstDay
FROM (VALUES
    (CURRENT_TIMESTAMP)
) AS t(ArrivalDate)

ArrivalDate             | FirstDay
2019-05-15 09:35:12.050 | 2019-05-01

But my favorite is EOMONTH which requires SQL Server 2012:

SELECT ArrivalDate
     , DATEADD(DAY, 1, EOMONTH(ArrivalDate, -1)) AS FirstDay
FROM (VALUES
    (CURRENT_TIMESTAMP)
) AS t(ArrivalDate)

ArrivalDate             | FirstDay
2019-05-15 09:35:52.657 | 2019-05-01

Answer 2

Use FORMAT to format your date.

DECLARE @date DATETIME = '2015-10-17 00:00:00.000'

SELECT FORMAT(@date, 'yyyy-MM-01 HH:mm:ss.fff')

Or if you don't want time part:

SELECT FORMAT(@date, 'yyyy-MM-01 00:00:00.000')

LiveDemo