Search code examples
sqlsql-server

Convert smallint into yyyy

I have a table1 with 20 columns one of which is year column as smallint in the format of YYYY.

I want to insert it into table2 with year column as Datetime but as date format YYYY.

insert into table2
     (Year)
Select 
     CONVERT(datetime,convert(varchar(10),year))
from table1

However it did not work with the error

Conversion failed when converting date and/or time from character string.

Any suggestions?

Solution

  • You want DateTimeFromParts():

    insert into table2
     (Year)
Select 
     DateTimeFromParts(year, 1, 1, 0, 0, 0, 0) 
from table1

    There's also DateFromParts() if you just need a date value (as well as options for DateTime2, DateTimeOffset, and SmallDateTime), but as the question states you have a DateTime column we'll make sure the types match cleanly.

    Do NOT use strings for this. Thanks to cultural/internationalization issues, using strings tends to be much slower and more error-prone than you'd want or expect, with lots of subtle "gotchas".