dbo.fx
--------------------
TimeMaxTemp
0349
0457
0527
0655
0704
数据类型是varchar(50)
=====================
TimeMaxTemp
3:49
4:57
5:27
6:55
7:04
数据类型char(8)
我想把上面的时间转换成下面的类型,中间加入“:”,貌似有点复杂,辛苦了
--------------------
TimeMaxTemp
0349
0457
0527
0655
0704
数据类型是varchar(50)
=====================
TimeMaxTemp
3:49
4:57
5:27
6:55
7:04
数据类型char(8)
我想把上面的时间转换成下面的类型,中间加入“:”,貌似有点复杂,辛苦了
INSERT TBTESTSELECT '0349' union
SELECT '0457' union
SELECT '0527' union
SELECT '0655' union
SELECT '0704' --drop table tbtestselect left(convert(int,TimeMaxTemp),1)+':'+right(convert(int,TimeMaxTemp),2) from tbtest
-------
3:49
4:57
5:27
6:55
7:04(所影响的行数为 5 行)
if object_id('[fx]') is not null drop table [fx]
go
create table [fx]([TimeMaxTemp] varchar(4))
insert [fx]
select '0349' union all
select '0457' union all
select '0527' union all
select '0655' union all
select '0704'
---查询---
select
cast(ltrim(left(TimeMaxTemp,2)+0)+':'+right(TimeMaxTemp,2) as char(8)) as TimeMaxTemp
from
fx
---结果---
TimeMaxTemp
-----------
3:49
4:57
5:27
6:55
7:04 (所影响的行数为 5 行)
select left(TimeMaxTemp*1,1)+':'+right(TimeMaxTemp*1,2) from fx
if object_id('dbo.tb') is not null
drop table dbo.tb;
go
create table tb(col varchar(50));
go
insert into tb select '0349'
union all select '0457'
union all select '0527'
union all select '0655'
union all select '0704'select
cast(left(col,2)+':'+right(col,2) as char(8)) as col from tb
INSERT TBTESTSELECT '0349' union
SELECT '0457' union
SELECT '0527' union
SELECT '0655' union
SELECT '0704' --drop table tbtestselect left(convert(int,TimeMaxTemp),1)+':'+right(convert(int,TimeMaxTemp),2) from tbtest
-------
3:49
4:57
5:27
6:55
7:04(所影响的行数为 5 行)
结果接近正确,不过结果都小于12:00,我的可是24小时制的
INSERT TBTESTSELECT '0349' union
SELECT '0457' union
SELECT '0527' union
SELECT '0655' union
SELECT '1655' union
SELECT '0704' --drop table tbtestselect ltrim(left(TimeMaxTemp,2)*1)+':'+ltrim(right(TimeMaxTemp,2)) from tbtest
-----------------
3:49
4:57
5:27
6:55
7:04
16:55(所影响的行数为 6 行)
(
[id] int identity(1,1) not null,
[TimeMaxTemp] varchar(50)
)--此处,插入数据做实验
insert t
SELECT '0349' union
SELECT '0457' union
SELECT '0527' union
SELECT '0655' union
SELECT '1655' union
SELECT '0704'
select * from t
update t
set TimeMaxTemp = cast(cast(left(TimeMaxTemp,2) as int) as varchar)+':'+cast(cast(right(TimeMaxTemp,2) as int) as varchar)
--更新完成后,更改表的结构,将TimeMaxTemp由varchar(50)改为char(8)
alter table t alter column TimeMaxTemp char(8)
select * from t
-----------------------------------
更改前
1 0349
2 0457
3 0527
4 0655
5 0704
6 1655
更改后
1 3:49
2 4:57
3 5:27
4 6:55
5 7:4
6 16:55
select cast(cast(left(TimeMaxTemp,2) as int) as char)+':'+right(TimeMaxTemp,2) as TimeMaxTemp from fx我的不行?
CREATE TABLE TBTEST(TimeMaxTemp varchar(50))
INSERT TBTESTSELECT '0349' union
SELECT '0457' union
SELECT '0527' union
SELECT '0655' union
SELECT '0704' select rtrim(cast(cast(left(TimeMaxTemp,2) as int) as char))+':'+right(TimeMaxTemp,2) as TimeMaxTemp from tbtestdrop table tbtest--结果
TimeMaxTemp
-----------------------------------
3:49
4:57
5:27
6:55
7:04
insert @T
select '0349' union
select '0457' union
select '0527' union
select '0655' union
select '1730' union
select '0704' select ltrim(0+left(TimeMaxTemp,2))+':'+ltrim(right(TimeMaxTemp,2)) [时间] from @T
时间
-----------------
3:49
4:57
5:27
6:55
7:04
17:30