CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
joelforsyth
joelforsyth

Reputation: 1021

Select a specific date for the current year

I have a query that needs to select {current year}-05-31 00:00:000. Is there a way I can do it without just concatenating the year on to that string?

Here's my current query:

SELECT DATEADD(dd, 30, DATEADD(mm, 4, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))

Upvotes: 2

Views: 4179

Answers (2)

Stephan
Stephan

Reputation: 6018

HVD's method is probably the simplest:

SELECT DATEADD(YEAR,YEAR(GETDATE()) - 2000,'20000531')

In SQL 2012 and above, they made it really easy.

SELECT DATEFROMPARTS(YEAR(GETDATE()),05,31)

Upvotes: 7

Tjasun
Tjasun

Reputation: 176

If you don't have performance issues you can use:

DATEADD(YEAR,DATEDIFF(YEAR,CAST('2018-05-31 00:00:000' AS DATETIME), GETDATE()),CAST('2018-05-31 00:00:000' AS DATETIME))

This is a solution for not concat the year.

Upvotes: 1

Related Questions