date h m
20100914 9 35
20100914 9 40
20100914 10 0
20100914 10 5合并成
date201009140935
201009140940
201009141000
201009141005h、m字段是一位就补0,例如9->09,0->00
20100914 9 35
20100914 9 40
20100914 10 0
20100914 10 5合并成
date201009140935
201009140940
201009141000
201009141005h、m字段是一位就补0,例如9->09,0->00
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([date] varchar(20),[h] int,[m] int)
insert [TB]
select '20100914',9,35 union all
select '20100914',9,40 union all
select '20100914',10,0 union all
select '20100914',10,5
GO--> 查询结果
SELECT * ,
RIGHT('00'+convert(varchar(2),h),2),
RIGHT('00'+convert(varchar(2),m),2),
convert(varchar(20),date)+RIGHT('00'+convert(varchar(2),h),2)+RIGHT('00'+convert(varchar(2),m),2)
FROM [TB]
--> 删除表格
--DROP TABLE [TB]
20100914 9 35 09 35 201009140935
20100914 9 40 09 40 201009140940
20100914 10 0 10 00 201009141000
20100914 10 5 10 05 201009141005
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (date datetime,h int,m int)
insert into #tb
select '20100914',9,35 union all
select '20100914',9,40 union all
select '20100914',10,0 union all
select '20100914',10,5select
date= convert(varchar(8),date ,112)+right('00'+ltrim(h),2)+right('00'+ltrim(m),2)
from #tbdate
----------------
201009140935
201009140940
201009141000
201009141005(4 行受影响)
from tb
select cast([date] as varchar(255))+'0'+cast([h] as varchar(255))+'0'+cast([m] as varchar(255)) from 表