First Day of Next Year for SQL Server?

Question

This should not be this hard. I simply need the following:

SET @DueDate = CONVERT (DATETIME, '01/01/2010')

However, I need it programatically so that if it were March of 2010, the date given would be '01/01/2011'.

I know it's simple, but my brain isn't coming up with it. I'm sure it's with a DateAdd and getdate().

Answer 1

Number of year boundaries between now and year zero less one (31 dec 1899), add back on.

SELECT DATEADD(year, DATEDIFF(year, -1, GETDATE()), 0)

Let's try a date next year to get 2011. because 1 Jan 2010 is start of next year in 2009...

SELECT DATEADD(year, DATEDIFF(year, -1, '2010-03-21'), 0)

Answer 2

Perhaps, create a table with first of the year dates, then pick the smallest that is larger than your date.

Perhaps simply put a string together, '01/01' + (MyYear + 1)?

Answer 3

Based on the Database Journal article: How to Calculate Different SQL Server Dates:

First Day of the Next Year

I use the year interval (yy) to display the first day of the next year.

select DATEADD(yy, DATEDIFF(yy, -1, getdate()), 0)

Answer 4

You could add one to the current year, then concat with Jan 01, then convert back to date:

select CONVERT(datetime, CONVERT(VARCHAR(20), YEAR(GETDATE())+1) + '.01.01')