如何使用SQL查询将日期范围一分为二? [英] How to split date range in to two using sql query?

问题描述

这是表格

this is the table

DECLARE @Product TABLE (Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values
(''20110320'', ''20110330'', 101)

DECLARE @Beg Datetime, @End Datetime
SET @Beg = ''20110324''
SET @End = ''20110326''

我希望将日期范围从20到30乘以24到26



我需要这样的输出

i wand to split my date range 20 to 30 by 24 to 26



I need my out put like this

20/03/2011  23/03/2011 101

27/03/2011  30/03/2011 101

what is the sql query for this

推荐答案

的sql查询是什么?问:在

Have to ask: Was there something wrong/not working with the answer in How to update a date range?[^] or is this a different situation?

[Addition]
Since this was a new case, perhaps something like this:

DECLARE @Product TABLE (Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values ('20110320', '20110330', 101)
DECLARE @Beg Datetime, @End Datetime
SET @Beg = '20110324'
SET @End = '20110326'

DECLARE @Temp TABLE (DateVal  Datetime, Date_Type int, Product_ID INT)

-- insert each date to temp tabl
insert into @Temp
select Date_From, 1, Product_ID
from @Product
union all
select Date_To, 2, Product_ID
from @Product

-- add the date variables to temp table NOTE fixed product so it possibly comes from a variable etc.
insert into @Temp
select @Beg, 2, 101
union all
select @End, 1, 101

-- remove existing rows, most likely here should be a WHERE clause restricting the deletion
delete from @Product;

-- recreate the product table based on temp table
insert into @Product
select  q1.dateval,
        ( select min(q2.dateval)
          from @Temp q2
          where q2.Date_type = 2
          and q2.dateval > q1.dateval),
        q1.Product_ID
from @Temp q1
where q1.Date_type = 1

SELECT * FROM @Product ORDER BY Date_From

很可能您必须添加不同种类的where子句等以使其适合您的需求，但是由于我只有1个测试用例数据，因此我对照该行为验证了行为.

希望它会有所帮助.

Most likely you have to add different kinds of where clauses etc to make this fit into your needs but since I had only 1 test case data I verified the behaviour against it.

Hopefully it helps.

这篇关于如何使用SQL查询将日期范围一分为二?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！