CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012
CodeNinja
CodeNinja

Reputation: 3278

Convert an int to date in SQL Server

Year(int)  Month(int)   ProductName     Date(DateTime)
   2013         11        ACB          
   2013         11        CDE

I am trying to update the Date values in the above table.

This query doesn't work.

Is there a way I can convert int to datetime ?

UPDATE t
SET t.[Date] = convert(date, convert(int, 
                       t.[Year]) + '-' + convert(int, t.[Month]) + '-01')
FROM Table t

Upvotes: 0

Views: 111

Answers (1)

anon
anon

Reputation:

You are trying to add integers and strings. This isn't Visual Basic 6.0; you need to be more explicit with your data type conversions, especially when trying to build strings out of numeric types.

Of course, there are easier and more efficient ways to do this than to bother with any clunky conversions to strings anyway:

UPDATE dbo.Table
  SET [Date] = DATEADD(MONTH, [Month]-1, DATEADD(YEAR, [Year]-1900, 0));

In SQL Server 2012 (not sure why you tagged 3 different versions):

UPDATE dbo.Table
  SET [Date] = DATEFROMPARTS([Year],[Month],1);

No reason in either case to use FROM...

Upvotes: 4

Related Questions