SQL Server 2012: Round to NEAREST(!) start of month (in timestamp format) from a timestamp column)

Question

I need to round to NEAREST start of month (in timestamp format) from a timestamp column.

How do one accomplish this?

Examples:

TimestampColumn A:             Rounded to these values
2012-01-07 18:18:29.923        2012-01-01 00:00:00.000
2012-01-14 12:58:13.122        2012-01-01 00:00:00.000
2012-06-09 17:10:30.787        2012-06-01 00:00:00.000
2012-05-31 09:29:43.870        2012-06-01 00:00:00.000
2012-10-22 12:09:47.067        2012-11-01 00:00:00.000
2012-10-15 04:35:11.013        2012-10-01 00:00:00.000

Answer 1

Consider converting to date first

DECLARE @d DATETIME
set @d = CONVERT(DATE, '2012-02-14 12:58:13.122')

SET @d =  DATEADD(DAY, 1-datepart(day, @d), @d)

SELECT @d

Answer 2

Here is one way to do it - just subtract all the parts of the date which you don't care about:

DECLARE @d DATETIME
set @d = '2012-02-14 12:58:13.122'

SET @d =  DATEADD(DAY, 1-datepart(day, @d), @d)
SET @d =  DATEADD(hour, -datepart(hour, @d), @d)
SET @d =  DATEADD(minute, -datepart(minute, @d), @d)
SET @d =  DATEADD(second, -datepart(second, @d), @d)
SET @d =  DATEADD(millisecond, -datepart(millisecond, @d), @d)

SELECT @d

Answer 3

Calculate the length of the applicable month in seconds and then decide whether you are past the middle of the month. Go forward or back as needed.

declare @Foo as DateTime = '2012-10-15 12:35:11.013'

select
  DateAdd( month, DateDiff( m, 0, @Foo ), 0 ) as 'Year/Month',
  DateDiff( s, DateAdd( month, DateDiff( m, 0, @Foo ), 0), @Foo ) as 'Seconds Into Month',
  DateDiff( s, DateAdd( month, DateDiff( m, 0, @Foo ), 0 ), DateAdd( month, DateDiff( m, 0, @Foo ) + 1, 0 ) ) as 'Seconds In Month',
  DateDiff( s, DateAdd( month, DateDiff( m, 0, @Foo ), 0 ), DateAdd( month, DateDiff( m, 0, @Foo ) + 1, 0 ) ) / 2 as 'Seconds In Half Month',
  DateAdd( month, DateDiff( m, 0, @Foo ) + Round( 1.0 * DateDiff( s, DateAdd( month, DateDiff( m, 0, @Foo ), 0), @Foo ) / DateDiff( s, DateAdd( month, DateDiff( m, 0, @Foo ), 0 ), DateAdd( month, DateDiff( m, 0, @Foo ) + 1, 0 ) ), 0 ), 0 ) as 'Rounded Date'

Answer 4

Thanks for contributing. But I'm going for this one; its good enough.

SELECT CASE
WHEN DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) > 
ABS(DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) 
THEN DATEADD(dd, datediff(dd, 0, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)), GETDATE() ) )+0, 0)
ELSE dateadd(dd, datediff(dd, 0, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)), GETDATE() ) )+0, 0)
END

Answer 5

Here is an example. You have 5 fields: the date you want to convert, the converted date as you wanted, the first day of the month, the last day of the month, and the first day of the next month. Just choose what you need in it:

SELECT
    BED_Meeting_When,
    CASE WHEN DAY(BED_Meeting_When) < 15 THEN (DATEADD(DAY, (-DAY(BED_Meeting_When) + 1), BED_Meeting_When)) ELSE DATEADD(DAY, (-DAY(BED_Meeting_When) + 1), DATEADD(MONTH, 1, BED_Meeting_When)) END,
    DATEADD(DAY, (-DAY(BED_Meeting_When) + 1), BED_Meeting_When) AS 'Arrondi au premier du mois',
    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, BED_Meeting_When) + 1, 0)) AS 'Arrondi au dernier du mois',
    DATEADD(DAY, (-DAY(BED_Meeting_When) + 1), DATEADD(MONTH, 1, BED_Meeting_When)) AS 'Arrondi au premier du mois suivant',
    BEMR_Titre
FROM bpri_entretien_detail
    INNER JOIN bpri_entretien_motif ON (BED_BEMR_Idx = BEMR_Idx)

Answer 6

Hi I know this is late to the party a little simple offering can never offend I hope

Creating a mini temp table I pop'd the dates offered in the original post

and then selected from it as below

create table #DT(

TS_A datetime, TS_B datetime)

insert into #DT (ts_a) values ('2012-01-07 18:18:29.923'), ('2012-01-14 12:58:13.122'), ('2012-06-09 17:10:30.787'), ('2012-05-31 09:29:43.870'), ('2012-10-22 12:09:47.067'), ('2012-10-15 04:35:11.013')

select TS_A, DATEADD(MONTH, DATEDIFF(MONTH, 0,ts_a), 0) TS_B from #DT

Results - I hope this is clear

RESULTS

Amazing how times they are a changing :-)

TX
I hope this turns out alright haven't posted any where for over a decade