显示存储在sql数据库中的日期之间的日期间隔 [英] Displaying Date Gaps Between dates Stored in sql DataBase

问题描述

我在表中存储了日期和数据
如

1
3
8
9
假设以上为日期字段
我只想显示
两者之间没有差距
1
2
3
4
5
6
7
----依此类推，直到最后一个Date字段
如何使用sql

I have Dates and Data Stored in table
as

1
3
8
9
assume above as date fields
I just Want to display
without leaving the gaps in between
1
2
3
4
5
6
7
---- and so on till the last Date Field
How can I achieve this.using sql

推荐答案

，可以使用 ^ ]和 DATEADD [

You can use WHILE[^] and DATEADD[^]

Psuedocode

WHILE( DATE < LastDate)
BEGIN
  IF EXISTS(SELECT DATE FROM table)
    CONTINUE
  ELSE
    DATEADD(day, 1, DATE)
END

您也可以使用它！！！

You can use this also!!!!

 with GETDATES as
 (
   select cast(STARTDATE as datetime) DateValue
    union all
   select DateValue + 1
    from  GETDATES    where   DateValue + 1 < ENDDATE
) select DateValue  from   GETDATES
 OPTION (MAXRECURSION 0)

实际上，您的问题不是很清楚，但是据我所知，如果您要列出数据库表日期条目中的所有日期字段，则分别为5,15,25.

日期字段按顺序返回5到25.....

首先获取开始日期和最后日期，然后使用BELOW SQL FUNTION

Actually your question is not so clear but as my understanding if u want the list all the date fields like in your DB table date entry are 5,15,25.

Date fields return 5 to 25 in sequence.....

First got the startdate and lastdate then use the BELOW SQL FUNTION

CREATE FUNCTION dbo.GenerateDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

这篇关于显示存储在sql数据库中的日期之间的日期间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！