为什么要转字符串比较阿?直接用时间比较不就行了.
create table #tb_ID ( --创建 [商品清单表]
spid varchar(50), -- 商品id
sp_name varchar(50), --商品名
pric decimal(12,2) --商品标准价格
)
insert into #tb_ID (spid,sp_name,pric) values ('01','苹果',21.00)
insert into #tb_ID (spid,sp_name,pric) values ('02','梨子',22.00)
select * from #tb_ID -- delete from #tb_ID
go
create table #tb_pr ( --创建 [价格区间表]
spid varchar(50), -- 商品id
begin_t datetime , --开始时间
end_t datetime , --结束时间
pric_qj decimal(12,2) ) --区间价格
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 20:00:00', '2000-01-01 20:59:59' , 10.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 21:00:00', '2000-01-01 21:59:59' , 11.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 22:00:00', '2000-01-01 22:59:59' , 12.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 23:00:00', '2000-01-01 23:59:59' , 13.00 ) --如果跨过第二天怎么弄呢?
select * from #tb_pr -- delete from #tb_pr
declare @now datetime
select @now =convert(datetime,'2000-01-01 21:58:00')
--select @nowselect a.sp_name
,isnull(
(select top 1 pric_qj from #tb_pr b where a.spid=b.spid and @now between b.begin_t and b.end_t)
,a.pric) as pric
from #tb_ID a
create table #tb_ID ( --创建 [商品清单表]
spid varchar(50), -- 商品id
sp_name varchar(50), --商品名
pric decimal(12,2) --商品标准价格
)
insert into #tb_ID (spid,sp_name,pric) values ('01','苹果',21.00)
insert into #tb_ID (spid,sp_name,pric) values ('02','梨子',22.00)
select * from #tb_ID -- delete from #tb_ID
go
create table #tb_pr ( --创建 [价格区间表]
spid varchar(50), -- 商品id
begin_t datetime , --开始时间
end_t datetime , --结束时间
pric_qj decimal(12,2) ) --区间价格
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 20:00:00', '2000-01-01 20:59:59' , 10.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 21:00:00', '2000-01-01 21:59:59' , 11.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 22:00:00', '2000-01-01 22:59:59' , 12.00 )
insert into #tb_pr (spid,begin_t,end_t,pric_qj) values ('01','2000-01-01 23:00:00', '2000-01-01 23:59:59' , 13.00 ) --如果跨过第二天怎么弄呢?
select * from #tb_pr -- delete from #tb_pr
declare @now datetime
select @now =convert(datetime,'2000-01-01 21:58:00')
--select @nowselect a.sp_name
,isnull(
(select top 1 pric_qj from #tb_pr b where a.spid=b.spid and @now between b.begin_t and b.end_t)
,a.pric) as pric
from #tb_ID a
--like this
SELECT CONVERT(CHAR(8),CAST('2015-01-30 00:00:01' AS DATETIME),112) --20150130
SELECT CONVERT(CHAR(8),CAST('2015-01-30 23:59:59' AS DATETIME),112) --20150130--between
SELECT CONVERT(CHAR(8),CAST('2015-01-29 00:10:01' AS DATETIME),112) --20150129--and
SELECT CONVERT(CHAR(8),CAST('2015-01-31 00:00:00' AS DATETIME),112) --20150131
如果 start_time > end_time ,则日期部份为当前日期
如果 start_time < end_time 则start_time的日期部分为当前日期,而end_time的日期为次日