Rounding dates to first day of the month [duplicate]

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]

Radon

Is that column actually a datetime field, or is it stored in some variation of varchar? – Lineman

It's a datetime field. – Radon

In which case it does not have a "format". You're really asking about needing to do some arithmetic with dates to find the first of the month for a given date. – Lineman

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

Gumboil

Exactly what I needed! – Radon

Perfect! Thank you sir! – Kyte

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

Trishatriskelion

Does the job! Highly appreciated. – Radon

With MySQL: DATE_FORMAT(@date, '%Y-%m-01') – Justify

Answer 3

Beginning with SQL Server 2012, you can also use DATEFROMPARTS:

SELECT DATEFROMPARTS(YEAR(ArrivalDate), MONTH(ArrivalDate), 1)
FROM   my_table

Biggers

Answer 4

Round date to first of the month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, DateColumn), 0)

Hepatic

Answer 5

Or just simply use the ROUND function -

SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') "New Year" FROM DUAL;

New Year

01-JAN-01

Nightgown

For a SQL Server question this look kind of Oraclish – Hepatic