php - Convert varchar column to datetime2 without affecting column data?

I need to change column datatype without affecting data in that column. I have table structure below:

CREATE TABLE [dbo].[PROJECT_LOG](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UNIT] [int] NULL,
    [NAME] [nvarchar](100) NULL,
    [STATUS] [numeric](1, 0) NULL,
    [LOG] [nvarchar](200) NULL,
    [LAST_UPDATE] [nvarchar](100) NULL
) ON [PRIMARY]
GO

Now, I have around 200 records below:

I want to change column LAST_UPDATE (current mm/dd/yyyy format) to datetime2. Can anyone help me with this?

I tried using convert query for the same as the guys suggested below and answer here.

-- Add new column.
ALTER TABLE [dbo].[PROJECT_LOG] ADD LAST_UPDATE_TIME  DATETIME;

-- Convert value.
UPDATE [dbo].[PROJECT_LOG]
   SET LAST_UPDATE_TIME = CONVERT(nvarchar, 
REPLACE(LEFT(LAST_UPDATE, 11), '/', ' ') + ' ' + RIGHT(LAST_UPDATE, 8), 101);

While executing query its throwing error:

Answer

Solution:

Changing the table

The approach:

Add a column with a substitute with a correct type (date recommended instead of datetime2(7)
Update this column with Convert( date, LAST_UPDATE, 101 )
Drop the original column
Rename the new column to the name of the original column

Important note: Check all the import scripts to this table to fix the functions used to set LAST_UPDATE.

Alternative

Add a column with name LAST_UPDATE_DATE type date as derived column
Derived column formula: AS Convert( date, LAST_UPDATE, 101 ) [PERSISTED]
Keep both values as imported and as needed

Important note: If you get any other date format other than US then this formula breaks as it explicitly expects the 101 US format.

View as crazy alternative

Build a view on top of this table that does the transformation. In SQL Server 2008 there is no TRY_CAST function to fail graciously.

Use the view for downstream work.

Why date?

Type date costs 3 bytes and is perfect for date only values.

datetime2(0) costs 6 bytes, the default datetime2(7) costs 8 bytes.

References:

Cast and Convert https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Datetime2 https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15

Try_Cast https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15

Answer

Solution:

Since this you are using the 2008 version try_cast will be not helpful.

One safe method will be to implement the update with a loop and go through the entire table row by row with a try catch block within the loop where you can handle in case of any failure at the time of casting , in catch block you can give the query to update the value as NULL to identify if casting failed.

Source