Changing column data from string to datetime data format in sql

Question

Any advice on how to change a date time value in a large table with many rows.

the current datatype is in string format and looks like this in the rows:

23/03/2022 00:00:00
23/03/2022 00:00:00
23/03/2022 00:00:00
23/03/2022 00:00:00
23/03/2022 00:00:00
23/03/2022 00:00:00

I have tried convert

SELECT CONVERT(DATETIME2(7),[start_date] )
   FROM 'table_name'

would like to change this to a datetime format, any advice is much appreciated

Date and time data types don't have a format; the format of the value is determined by the presentation layer not the SQL Layer. If you don't know how to change the displayed format in the application your creating, then that is what you should be asking about. — Thom A, Commented May 20, 2022 at 10:31
If, however, you are storing date and time data as a string based data type ((n)(var)char) then you have a design flaw and should be fixing your design to store the data in an appropriate date and time data type. — Thom A, Commented May 20, 2022 at 10:32
I would add a new column, then fill it with values (update table set new_col = convert(datetime2(7), old_column), then drop old column — Limonka, Commented May 20, 2022 at 10:46
You'll need UPDATE dbo.table SET new_col = CONVERT(datetime2(7), old_col, 103);. The 103 is important as it tells SQL Server to interpret it as d/m/y regardless of your language / dateformat settings. I don't think there is going to be a reliable way to convert the existing column to the new type without some kind of transfer, because you can't say ALTER COLUMN <newtype, style number>. Hopefully this is a good lesson in storing data in the right data type in the first place. — Aaron Bertrand, Commented May 20, 2022 at 11:20
@AlwaysLearning Right, I meant in a single step (what you're suggesting I'd still qualify as a "transfer" first, but using a second column is safer because you don't risk losing any of the original data). And even that is only reliable if all the dates are in fact in d/m/y and they're all valid dates. — Aaron Bertrand, Commented May 20, 2022 at 11:38

Aaron Bertrand · Accepted Answer · 2022-05-20 14:04:18Z

To avoid potential data loss (or almost certain failure) trying to update the column's data type directly, here is my suggestion:

Add a new column:

ALTER TABLE dbo.table_name
  ADD some_new_column datetime2(7) NULL;

Try to update that column:

UPDATE dbo.table_name 
  SET some_new_column = TRY_CONVERT(datetime2(7), 
    original_column, 103) -- important for d/m/y
  WHERE original_column IS NOT NULL;

Identify any rows where the original value couldn't be converted (and fix them, depending on what you find):

SELECT original_column
  FROM dbo.table_name
  WHERE original_column IS NOT NULL
    AND some_new_column IS NULL;

Once all the data is fixed, drop the original column and rename the new one to the old name:

ALTER TABLE dbo.table_name DROP COLUMN original_column;

EXEC sys.sp_rename N'dbo.table_namesome_new_column',
     N'original_column', N'COLUMN';

Example db<>fiddle

Repeat this process for any column where you are storing dates as strings (and in the future make sure to use the right data types from the start). More reading:

Collectives™ on Stack Overflow

Changing column data from string to datetime data format in sql

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related