How to change datetime2 data type to INT

 Dear Friend,

i am come with new solution if you stuck some place your are not able to  ALTER COLUMN the column if column data type is datetime2  so without delayed below is solution.

if you tried with normal query just like ALTER TABLE table ALTER COLUMN [Period] int; 

The Error is :- Operand type clash: datetime2 is incompatible with int?

The error you’re encountering indicates that the Period column currently has a datetime2 data type, and you’re trying to change it to int. Directly changing the data type from datetime2 to int is not allowed because they are incompatible types.

To resolve this, you need to follow these steps:

1. Create a New Column:

• Add a new column with the desired data type (int).-- Step 1: Add a new column with the desired data type

• ALTER TABLE tableName ADD PeriodInt INT;

2. Update the New Column:

• Populate the new column with the converted values from the existing column.

• -- Step 2: Update the new column with converted values
  UPDATE tableName 
  
  SET PeriodInt 
  = YEAR(Period) * 100 + MONTH(Period);

3. Drop the Old Column:

• Remove the old column.

• -- Step 3: Drop the old column
  ALTER TABLE tableName 
  DROP COLUMN Period;

4. Rename the New Column:

• Rename the new column to the original column name.

•  Step 4: Rename the new column to the original column name
  EXEC sp_rename 'tableName 
  .PeriodInt', 'Period', 'COLUMN';

Thank you for your Time Hope it will help you