how to extract current year and combine with specific month and date as a date

Question

I have to get the current year with June the 16th as a date.

For example, if I run the query now I want to get 2015-06-16.

I tried the following:

CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS VARCHAR(4))+'-06-16 00:00:00.000' as varchar (24) ),102)

but it didn't work.

I'm guessing you are using sql-server? Have you seen the DATEADD function? — James Anderson, Jun 4 '15 at 19:32
Do you want your query to always return June 16th of the current year? — James Anderson, Jun 4 '15 at 19:39
If you are using SQL server 2012 then you can use DateFromParts(Year(GetDate()), 6, 16). Check the link for further info : msdn.microsoft.com/en-us/library/hh213228.aspx — SelmanAY, Jun 4 '15 at 19:50

James Anderson · Accepted Answer · 2015-06-04 19:46:56Z

1

The following T-SQL gives you the result you need.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate()))+'-06-16')

answered Jun 4 '15 at 19:46

James Anderson

5,6252 gold badges22 silver badges41 bronze badges

Also: SELECT CONVERT(date, CONVERT(VARCHAR(4), GETDATE(), 112)+'0616'); (this will also work for dates before the year 1000). Your expression will need left padding with 0s to work for such dates.
– ypercubeᵀᴹ
Jun 4 '15 at 22:24

Add a comment |

1 Answer 1