34,838
社区成员




create table t1(
myid int,
mydate varchar(50)
)
insert into t1 select 1,'07 24 1999 12:00AM'
insert into t1 select 2,'07 24 2005 12:00AM'
insert into t1 select 3,'11 24 1999 12:00AM'
insert into t1 select 4,'01 12 1970 12:00AM'
select * from t1
/*
myid mydate
----------- --------------------------------------------------
1 07 24 1999 12:00AM
2 07 24 2005 12:00AM
3 11 24 1999 12:00AM
4 01 12 1970 12:00AM
(所影响的行数为 4 行)
*/
update t1
set mydate = convert(char(10),cast(left(mydate,10) as datetime),120)
select * from t1
/*
myid mydate
----------- --------------------------------------------------
1 1999-07-24
2 2005-07-24
3 1999-11-24
4 1970-01-12
(所影响的行数为 4 行)
*/
select convert(varchar(10),时间列,120) from 表
create table t1(
myid int,
mydate varchar(50)
)
insert into t1 select 1,'07 24 1999 12:00AM'
insert into t1 select 2,'07 24 2005 12:00AM'
insert into t1 select 3,'11 24 1999 12:00AM'
insert into t1 select 4,'01 12 1970 12:00AM'
select * from t1
/*
myid mydate
----------- --------------------------------------------------
1 07 24 1999 12:00AM
2 07 24 2005 12:00AM
3 11 24 1999 12:00AM
4 01 12 1970 12:00AM
(所影响的行数为 4 行)
*/
update t1
set mydate = substring(mydate,7,4)+'-'+substring(mydate,1,2)+'-'+substring(mydate,4,2)
select * from t1
/*
myid mydate
----------- --------------------------------------------------
1 1999-07-24
2 2005-07-24
3 1999-11-24
4 1970-01-12
(所影响的行数为 4 行)
*/
declare @t varchar(50),
@t2 varchar(50)
set @t='07 24 1999 12:00AM'
set @t2=substring(@t,7,4)+'-'+substring(@t,1,2)+'-'+substring(@t,4,2)
select @t as 以前格式,@t2 as 新格式
/*
以前格式 新格式
-------------------------------------------------- --------------------------------------------------
07 24 1999 12:00AM 1999-07-24
(所影响的行数为 1 行)
*/
declare @t varchar(50),
@t2 varchar(50)
set @t='07 24 1999 12:00AM'
set @t2=substring(@t,7,4)+'-'+substring(@t,1,2)+'-'+substring(@t,4,2)
select @t as 以前格式,@t2 as 新格式
declare @Date varchar(30)
set @Date = '07 24 1999 12:00AM'
Select convert(varchar(10), cast(@Date as datetime), 120)