如何填写列之间的最小和最大日期之间的日期 [英] How to fill in the dates in between a min and a max date from a column

问题描述

我希望我能够很好的解释这个问题。我正在尝试创建一个日期字段的表，该表使用另一个表的日期列来获取该列的最小和最大日期，并填写其中的所有日期。

所以如果最小和最大日期是1/1/2016和6/1/2016，那么我希望表格列出这些日期之间的每个日期。 / p>

我正在使用SQL Server Management Studio 2014。

感谢您的帮助。

解决方案

如果您有权力，那么我建议您在数据库中创建一个日期表。我发现有一个日期表非常有用于纪念（并集中）关于日期的某些属性以及这些类型的需求（即使它涉及到，等待它，三角形加入）。

创建日期表...

它很简单，可以随意加载列，即使它们是可以使用本机SQL Server函数计算的东西。让他们物理存储有时提供更多的灵活性和性能提升。

 创建表Dt（
 Dt date primary密钥聚类，
 BestDayEverBit位不为空default（0））; 
 
 insert Dt（Dt）
 select top（100000）
 dateadd（day，（row_number（）over（order by（select null））），convert（date， 1900'））
 from sys.all_columns a 
 cross join sys.all_columns b; 
  

查询...

 声明
 @BegDt date ='2016-02-03'，
 @EndDt date ='2016-02-13'; 
 
从Dt 
中选择* 
其中Dt> = @BegDt 
和Dt < @EndDt; 
  

结果

  Dt BestDayEverBit 
 ---------- -------------- 
 2016-02-03 0 
 2016-02-04 0 
 2016-02-05 0 
 2016-02-06 0 
 2016-02-07 0 
 2016-02-08 0 
 2016-02-09 0 
 2016-02-10 0 
 2016-02-11 0 
 20162-12 0 
  

希望这有帮助。

I hope I can explain this well enough. I'm trying to create a table with a date field that uses a date column from another table to grab the min and max dates from that column, and fills in all the dates in between.

So if the min and max dates were 1/1/2016 and 6/1/2016 then I would want the table to list every date in between those dates.

I am using SQL Server Management Studio 2014.

Thanks for your help.

解决方案

If you wield the power, then I propose that you create a "date" table in your database. I find having a "date" table extremely useful for memorializing (and centralizing) certain attributes regarding dates as well as these types of needs (even if it involves a, wait for it, triangular join).

Creating the date table ...

It's pretty straight forward and feel free to load it up with columns, even if they are something that can be computed using native SQL Server functions. Having them physically stored sometimes provides more flexibility as well as a performance boost.

create table Dt (
    Dt date primary key clustered,
    BestDayEverBit bit not null default(0));

insert Dt (Dt)
select top (100000)
    dateadd(day, (row_number() over (order by (select null))), convert(date, '1900'))
from sys.all_columns a
cross join sys.all_columns b;

The query ...

declare
    @BegDt date = '2016-02-03',
    @EndDt date = '2016-02-13';

select *
from Dt
where Dt >= @BegDt
    and Dt < @EndDt;

Results

Dt         BestDayEverBit
---------- --------------
2016-02-03 0
2016-02-04 0
2016-02-05 0
2016-02-06 0
2016-02-07 0
2016-02-08 0
2016-02-09 0
2016-02-10 0
2016-02-11 0
2016-02-12 0

Hope this helps.

这篇关于如何填写列之间的最小和最大日期之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！