- SQL
- Microsoft SQL Server
- Query Syntax
How can I convert a column that is varchar(50) to Date time
I want to convert a table column to DATETIME, I used alter table name [Dim_Loan] alter column LoanDate datetime, got an error, Conversion failed when converting date and/or time from character string . The column LoanDate is varchar(13), LoanDate column has this output Jan 11 2020 1, am not sure what the 1 stands for. The Loan Date is being used in a store procedure as a Report Date with datetime as its data type
You'll have to remove the trailing '1' or it's not a valid date so it won't convert.
You could try stripping everything after the date part:
UPDATE dbo.Dim_Loan
SET LoanDate = LEFT(LoanDate, 11)
WHERE LoanDate LIKE '[A-Z][a-z][a-z] [0-9]%[2][0-9][0-9][0-9]%'
Then try ALTERing the column again.
I updated the column now it shows Loan Date Jan 11 2020 but Alter column LoanDate datetime throws " Conversion failed when converting date and/or time from character string" The statement has been terminated.
Not sure what happened earlier but now it works, I had scrolled down the table and notice some rows had the 1 so I updated the whole table again,
2020-01-14 is the new data type,
2020-01-14 is the new data type,
Premium Content
You need an Expert Office subscription to comment.Start Free Trial