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]

Is that column actually a `datetime` field, or is it stored in some variation of `varchar`? — Jamiec, Oct 19 '15 at 11:30
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. — Jamiec, Oct 19 '15 at 11:34

Salman A · Accepted Answer · 2019-05-02T16:34:17.710

19

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

edited May 02 '19 at 16:34

answered Oct 19 '15 at 11:31

Salman A

262,204
82
430
521

Exactly what I needed! – user3115933 Oct 19 '15 at 12:00
Perfect! Thank you sir! – JwJosefy Jun 18 '17 at 22:24

score 13 · Answer 2 · answered Oct 19 '15 at 11:33

13

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

answered Oct 19 '15 at 11:33

Lukasz Szozda

162,964
23
234
275

2

Does the job! Highly appreciated. – user3115933 Oct 19 '15 at 12:01
With MySQL: `DATE_FORMAT(@date, '%Y-%m-01')` – asachet Jul 19 '19 at 15:28

score 11 · Answer 3 · answered Oct 19 '15 at 18:08

11

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

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

answered Oct 19 '15 at 18:08

Code Different

90,614
16
144
163

score 8 · Answer 4 · answered Oct 19 '15 at 18:02

8

Round date to first of the month:

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

answered Oct 19 '15 at 18:02

Metaphor

6,157
10
54
77

score -3 · Answer 5 · answered Aug 11 '18 at 15:34

-3

Or just simply use the ROUND function -

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

New Year

01-JAN-01

answered Aug 11 '18 at 15:34

Ryan Kramer

1

4

For a SQL Server question this look kind of Oraclish – Metaphor Aug 08 '19 at 19:56

Rounding dates to first day of the month

5 Answers5

New Year