We help IT Professionals succeed at work.

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
Comment
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

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.


Author

Commented:
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.

Author

Commented:
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,

Author

Commented:
the first update went halfway down the table am not sure why, but I scrolled down the table and noticed some rows still had the 1, I updated the table and was able to convert the Loan Date column to date, am curious what this means '[A-Z][a-z][a-z] [0-9]%[2][0-9][0-9][0-9]%'
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

What the pattern means is the characters must be:

letter

letter

letter

space

number

<any number of any type of chars> [to allow for Jan 1 yyyy and Jan 11 yyyy, just in case a single-digit day is allowed]

'2'

number

number

number

<any number of any type of chars>