Printable View
I have a char(30) variable in the database that in some instances it has 2/2/2009 and some have 02/02/2009. I am wanting to write an sql update query that goes through and updates all instance of m/d/yyyy to mm/dd/yyyy how do I do this being that it is of type char?
This was a result of data entry problem, different people entered data in different ways, and now that we have to go back through and change it.
use tempdb
create table test (id int, date char(30))
insert into test values (1,'2/2/2009')
insert into test values (1,'12/2/2009')
insert into test values (1,'02/2/2009')
insert into test values (1,'12/02/2009')
--Fix month part
update test set date=case when substring(date,2,1) ='/' then '0'+date
else date end
select * from test
--Fix day part
update test set date=case when substring(date,5,1) ='/' then (substring(date,1,3)+'0'+substring(date,4,7) )
else date end
select * from test
or
use tempdb
create table test (id int, date char(30))
insert into test values (1,'2/2/2009')
insert into test values (1,'12/2/2009')
insert into test values (1,'02/2/2009')
insert into test values (1,'12/02/2009')
update test set date = convert(varchar(30),convert(datetime,date),101)
select * from test