Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query

Question

alter table Garantor alter column [Birth Date] int

Answer 1

Convert does not work for me. Cast does not work either. To change the data type of a column, here is the code that worked. Change the "date" type to the "int" type, keeping only the year.

This works for me:

ALTER TABLE [dbo].[Garantor]
ADD [newBirthDate] int NOT NULL DEFAULT 0
GO

UPDATE [dbo].[Garantor]
SET [newBirthDate] = DATEPART(yyyy,[Birth Date])
GO

ALTER TABLE [dbo].[Garantor]
DROP COLUMN [Birth Date]
GO

SP_RENAME 'dbo.Garantor.newBirthDate', 'dbo.Garantor.[Birth Date]'
GO

An alternative solution is:

= YEAR([Birth Date])

And if you have an index in your table:

ALTER TABLE [dbo].[Garantor]
ADD [newBirthDate] int NOT NULL DEFAULT 0
GO

UPDATE [dbo].[Garantor]
SET [newBirthDate] = DATEPART(yyyy,[Birth Date])
GO

ALTER TABLE [dbo].[Garantor] DROP CONSTRAINT [UQ__Garantor__1C123681D17FE31B] -- [UQ__Garantor__1C123681D17FE31B] change with your
GO

ALTER TABLE [dbo].[Garantor]
DROP COLUMN [Birth Date]
GO

SP_RENAME 'dbo.Garantor.newBirthDate', 'dbo.Garantor.[Birth Date]'
GO

ALTER TABLE [dbo].[Garantor] ADD UNIQUE NONCLUSTERED 
(
    [Birth Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Answer 2

Try something like this (you'll need to create a new column, update it with a conversion, drop the old one then rename the new one with old one's name)

ALTER TABLE dbo.Garantor
ADD newBirthDate int NOT NULL DEFAULT 0 -- NULL and DEFAULT as required
GO

UPDATE dbo.Garantor
SET newBirthDate = CAST([Birth Date] AS int) -- or CONVERT function, it will do the same
GO

ALTER TABLE dbo.Garantor
DROP COLUMN [Birth Date]
GO

SP_RENAME 'dbo.Garantor.newBirthDate', 'dbo.Garantor.[Birth Date]'
GO