-- Step 1: Create a new table with the correct data types
CREATE TABLE CovidData_New (
    DataAsOf DATE,
    StartDate DATE,
    EndDate DATE,
    [Group] VARCHAR(255),
    Year VARCHAR(255),
    Month VARCHAR(255),
    MMWRWeek INT,
    WeekEndingDate DATE,
    HHSRegion VARCHAR(255),
    RaceAndHispanicOriginGroup VARCHAR(255),
    AgeGroup VARCHAR(255),
    Covid19Deaths INT,
    TotalDeaths INT
);

-- Step 2: Insert data into the new table, converting columns as needed
INSERT INTO CovidData_New (DataAsOf, StartDate, EndDate, [Group], Year, Month, MMWRWeek, WeekEndingDate, HHSRegion, RaceAndHispanicOriginGroup, AgeGroup, Covid19Deaths, TotalDeaths)
SELECT 
    TRY_CAST(DataAsOf AS DATE),                       -- Safely casting to DATE
    TRY_CAST(StartDate AS DATE),                      -- Safely casting to DATE
    TRY_CAST(EndDate AS DATE),                        -- Safely casting to DATE
    [Group],                                          -- No conversion for VARCHAR columns
    Year,                                             -- No conversion for VARCHAR columns
    Month,                                            -- No conversion for VARCHAR columns
    TRY_CAST(MMWRWeek AS INT),                        -- Safely casting to INT
    TRY_CAST(WeekEndingDate AS DATE),                 -- Safely casting to DATE
    HHSRegion,                                        -- No conversion for VARCHAR columns
    RaceAndHispanicOriginGroup,                       -- No conversion for VARCHAR columns
    AgeGroup,                                         -- No conversion for VARCHAR columns
    TRY_CAST(Covid19Deaths AS INT),                   -- Safely casting to INT
    TRY_CAST(TotalDeaths AS INT)                      -- Safely casting to INT
FROM CovidData;

-- Step 3: Drop the old table
DROP TABLE CovidData;

-- Step 4: Rename the new table to the original table name
EXEC sp_rename 'CovidData_New', 'CovidData';