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?
7
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”.
5
You need to provide the day and month for that conversion to work. try something like this:
declare @table1 table([year] smallint)
insert into @table1(year) values(2024),(2016),(2015),(2023)
Select
CONVERT(datetime,convert(varchar(10),year)+'-01-01')
from @table1
3