Imported a date column from a CSV file where there are string values are shown as

I want to convert the entire column into a date format as

I have used this script which allowed me to generate the result.

SELECT dateadd(d,44703,'1899-12-30') as date

The question is, how could I apply this script for a range of values (there are 700 rows in the table). e.g 44000 to 44705. I would like all string values to be converted as a date format.

CodePudding user response：

To convert a string to date we can add temporary date column and fill it and delete old column and rename new one to old one

alter table TableName add NewColumnName date null; --create temporary column
update TableName set NewColumnName =dateadd(d,cast([date] as int),'1899-12-30') --fill it
alter table TableName drop column [date]--delete old column
EXEC sp_RENAME 'TableName.NewColumnName' , 'date', 'COLUMN'--rename new one to old one

CodePudding user response：

You could add a computed column with your existing statement as its definition, for example:

alter table <yourtable> 
  add <NewColumnName> as 
  dateadd(day, convert(int,date), '18991230') 

Note - remove ambiguity and use day not d, and likewise always quote dates in ISO format.