create table t_003(date char(20)) go insert into t_003 select '20030528' insert into t_003 select '05/08/2003' insert into t_003 select '05-08-2003' insert into t_003 select '08 30 2003 12:00am' insert into t_003 select '2003' insert into t_003 select '2003-1-38'declare @date char(20) declare @a table(date int) declare cur cursor for select cur=[date] from t_003 open cur fetch next from cur into @date while @@fetch_status = 0 begin insert into @a values(datepart(yy,convert(datetime,replace(replace(@date,':',':'),'-','-')))) fetch next from cur into @date end select date from @a close cur deallocate cur go
嘿嘿,不好意思,看错了。楼上的应该可以。用这个试试也行:select year(cast(substring(a,1,charindex(',',a,1)-1) as datetime)) from aaa where a not like '%-%'union allselect left(a,4) from aaa where a like '%-%'
create table t_003(date char(20))
go
insert into t_003 select '20030528'
insert into t_003 select '05/08/2003'
insert into t_003 select '05-08-2003'
insert into t_003 select '08 30 2003 12:00am'
insert into t_003 select '2003'
insert into t_003 select '2003-1-38'declare @date char(20)
declare @a table(date int)
declare cur cursor for
select cur=[date] from t_003
open cur
fetch next from cur into @date
while @@fetch_status = 0
begin
insert into @a values(datepart(yy,convert(datetime,replace(replace(@date,':',':'),'-','-'))))
fetch next from cur into @date
end
select date from @a
close cur
deallocate cur
go
date
1 2003
2 2003
3 2003
4 2003
5 2003
6 2003
from aaa
where a not like '%-%'union allselect left(a,4)
from aaa
where a like '%-%'
select convert(char(4),convert(datetime,列名),112)
from table