Defaulting a column to the 1st of next month

Question

I'm trying to find the most elegant means of defaulting a column's value to the 1st of next month. The best I've been able to come up with is this:

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DEFAULT(DATEFROMPARTS(DATEPART(year, DATEADD(month, 1, GETDATE())), DATEPART(month, DATEADD(month, 1, GETDATE())), 1))

Whilst this works, it feels really clunky because I need to calculate DATEADD(month, 1, GETDATE()) twice and because I need to do the DATEFROMPARTS dance.

Is there a simpler way to achieve my goal?

Answer 1

It think this would be the simplest way to do it.

DATEADD(DD,1,EOMONTH (GETDATE()))

So you query would be like

ALTER TABLE Foo ADD
Bar datetimeoffset(0) NOT NULL DATEADD(DD,1,EOMONTH (GETDATE()));

Thanks!