convert integer column to date column yyyy sqlserver

Question

I want to change data type of a column on a db in sqlserver 2019. the data is stored in the column as integer and i want to convert it to date format yyyy. the data stored could be 2016 OR 2017 etc. How can I do that please ? THANK YOUU

Answer 1

You can use DATEFROMPARTS to get the first day in every month

SELECT DATEFROMPARTS(yearInt, monthInt, 1), OtherColumns
FROM YourTable;

Or for the first day in each year

SELECT DATEFROMPARTS(yearInt, 1, 1), OtherColumns
FROM YourTable;

You can also group up by that value:

SELECT DATEFROMPARTS(yearInt, 1, 1), OtherColumns
FROM YourTable
GROUP BY 
  DATEFROMPARTS(yearInt, 1, 1);

---

Or to create a new column:

ALTER TABLE YourTable
  ADD YourDateColumn date NOT NULL;

UPDATE YourTable
SET YourDateColumn = DATEFROMPARTS(yearInt, monthInt, 1);