SQL日月年转换年月日

方法比较砍,请高手赐教!

--第一步查出日期在前且不包含0的 修改添加0

select elder_name,birthrate as olddate,'0'+birthrate as newdate  from t_elder_base_info 
where birthrate!='' and substring(birthrate,2,1)='-' 

update t_elder_base_info set birthrate = '0'+ birthrate  
where birthrate!='' and substring(birthrate,2,1)='-' 

 
--第二步查询出日期在前面的 修改至后面 (日月年修改为年月日格式)

select elder_name,birthrate as olddate,substring(birthrate,7,4)+substring(birthrate,3,3) +'-'+substring(birthrate,1,2) as newdate 
from t_elder_base_info 
where birthrate!='' and substring(birthrate,3,1)='-' 

update t_elder_base_info set 
birthrate = substring(birthrate,7,4) + substring(birthrate,3,3) + '-'+substring(birthrate,1,2) 
where birthrate!='' and substring(birthrate,3,1)='-'