Clever Geek Handbook

First day of next year for SQL Server?

It doesn't have to be that hard. I just need the following:

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

However, I need it pragmatically, so if it were in March 2010, the date would be "01/01/2011".

I know it's easy, but my brain doesn't fit. I'm pretty sure this is with DateAdd and getdate()

.

+2


source to share


6 answers


The number of year boundaries between zeros and years is less than one (Dec 31, 1899), add back.

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


Try next year's date to get 2011. Because January 1, 2010 is the beginning of next year in 2009 ...

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


source


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

First day of next year



I am using a year interval (yy) to display the first day of the next year.

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

+2


source


By the way, as of SQL 2012, you can do DATEFROMPARTS(YEAR(@date) + 1, 1, 1)

. In your caseDATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1)

Hat tip fooobar.com/questions/1452212 / ...

+1


source


Perhaps create a table with the dates of the first year, then choose the smallest one that is greater than your date.

Maybe just put the line together, '01 / 01 '+ (MyYear + 1)?

0


source


You can add it to the current year and then join January 01 and then convert back:

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


source


Using some parts of the examples above, you can do this as well. This now assumes that your acceptable date format is Md-yyyy.

DATEADD(year, 1,'1-1-'+convert(varchar,DATEPART(YYYY,GetDate())))

Note. You get the current date, get the year part, create a string that converts to a date, and then add it to the year.

0


source






More articles:


All Articles