SQL Server date time convert

Question

I have a field in my database table called Month which holds its values as an INT it has the values 1 to 12 - I would like a way to convert 1 to read 1-11-2016 00:00:00 ie as a datetime field, 2 to read 1-12-2016 00:00:00, 3 to read 1-1-2017 00:00:00 - how can I convert a value like this? I am ok with doing case to switch but the convert/cast is confusing me...

Answer 1

You need to define a startdate (either by parameter or in a CTE), then just use dateadd()

@StartDate = '2016-10-01 00:00:00'

select dateadd(mm, t1.month, @StartDate) as NewMonth
from MyTable t1

Answer 2

Another response is

SELECT CONVERT(DATE, '2016-' + CAST(id AS VARCHAR(2)) + '-01') AS myDate
FROM T1

But y prefer JohmHC answer !!