CODEWITHSUNDEEP
sqlsql-server
Abdulsalam Elsharif
Abdulsalam Elsharif

Reputation: 5101

Convert INT column to Datetime in SQL Server

I have a column as int and it stores year like 2017.

How can I convert this to save the result to another actual datetime column like 2017-01-01?

Upvotes: 1

Views: 1317

Answers (4)

McNets
McNets

Reputation: 10807

DATEFROMPARTS is a new SQL Server function, (from SQL Server 2012), that allows to build a date value using its parts: Year, Month, Day.

Have a look at DATEFROMPARTS at MS Docs.

UPDATE TableName
SET    <UpdColName> = DATEFROMPARTS(<IntColName>,1,1)
WHERE  <some condition>

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is complicated. First, convert the column to a varchar():

alter table t alter column col varchar(255);

When you do this, the integer will be converted to a varchar().

Next, append '0101' to the value:

update t
    set col = col + '0101';

This puts the value in the form 'YYYYMMDD', which SQL Server recognizes as a date.

Finally, alter to a date:

alter table t alter column col date;

If you like, you can add another column to the table for the date and do this in two steps:

alter table t add column datecol date;

update t
    set datecol = concat(intcol, '0101');

Although you can also use datefromparts(), the above should work in earlier versions of SQL Server as well.

Upvotes: 1

Kushan
Kushan

Reputation: 10695

Try using DATEFROMPARTS,

UPDATE table SET coldatetime= datefromparts(colname,1,1) WHERE colname = 2017

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use the function DATEFROMPARTS.

select datefromparts(colname,1,1)
from tablename

Upvotes: 1

Related Questions