Change Datatype Varchar to Date.

Dipak Nile

6y1.2kSQL

I have inserted 5000 records using datatype varchar but now,

I want to Change Datatype varchar to Date?

can I change this datatype?

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
ALTER TABLE dbo.tablename ADD newcol DATETIME;
UPDATE dbo.tablename
  SET newcol = CONVERT(DATETIME,
   REPLACE(LEFT(varchar_col, 11), '/', ' ')
       + ' ' + RIGHT(varchar_col, 8), 113);
Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.
Amira Bedhiafi5y ago
What RDBMS you are using ? Can you please share a simple of your data ?
Ajay Gohil5y ago
Hello

You can refer to the following link:

https://searchoracle.techtarget.com/answer/Converting-a-column-from-VARCHAR2-to-DATE

https://stackoverflow.com/questions/39617862/sql-server-convert-varchar-to-date-in-alter-table
Jignesh Kumar6y ago
Hi Deepak,

Please use this link which will solve your problem,
https://dba.stackexchange.com/questions/17904/convert-varchar-column-to-datetime
Jaimin Shethiya6y ago
Hello Dipak,
Yes you have to change that
first you have to convert varchar value to datetime value and update that is same property and after converted all the data then you have to update that column datatype to datetime.

Thanks
YaminiKumar Boyapati6y ago
Three ways you can do it.
1) As you created it with datatype varchar let it be so and where ever you are showing, convert it to date like
CAST('2020-03-04' as date)
2) Create a new column with Datatype Date and update all the records into it and then delete the varchar column.
3) Lastly, convert that column data into "yyyy-mm-dd" format which would be bit hard and then convert the column into date type.
Bhawna Tuteja6y ago
You can do this by using convert function and then date time format
Amit Gupta6y ago
Simply create a new column then update the value from your varchar column by converting it to datetime and finally delete the varchar column
Piyush Pansuriya6y ago
Hi,
You can do, but first you have to change the date format which you have inserted. It should be 'yyyy-MM-dd'.
For that you have to update the table first.
May I know in which format you have inserted the date part?