CODEWITHSUNDEEP
sqlsql-server
clayton55
clayton55

Reputation: 33

How to convert a string column into a date column

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

date
44705
44704
44703

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

date
"2022-05-22"
"2022-05-21"
"2022-05-20"

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.

Upvotes: 1

Views: 1278

Answers (2)

JonTout
JonTout

Reputation: 640

select cast (44705-1 as smalldatetime) gives 2022-05-25 00:00

So you could just update the column using the above.

See https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=dc24abb3025e0f3796e7d978ba406be3

New fiddle with update statement, this line will update all rows, as per test.

update #test
set pdate = cast(dateadd(d,tdate-2,'1899-12-30') as smalldatetime)

Upvotes: 1

novice in DotNet
novice in DotNet

Reputation: 791

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(cast([date] as float) as int)-2,'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

Upvotes: 0

Related Questions