-
Convert char date m/d/yyyy to mm/dd/yyyy?
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|