Displaying Date Gaps Between dates Stored in sql DataBase

Question

0.00/5 (No votes)

See more:

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

Posted 23-Apr-12 1:43am

Karwa_Vivek

Updated 23-Apr-12 1:44am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Not Active · Answer 1 · 2012-04-23T02:08:00

Solution 2

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

Psuedocode

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

Posted 23-Apr-12 2:08am

Not Active

Arav Pradeep Gupta · Answer 2 · 2012-04-23T02:14:00

Solution 3

You can use this also!!!!

SQL

 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)

Posted 23-Apr-12 2:14am

Arav Pradeep Gupta

Updated 23-Apr-12 4:46am

v2

Comments

[no name] 23-Apr-12 10:46am

Only the code snippets need to be formatted. Not all of the text.

[no name] 23-Apr-12 10:48am

CTE is only available in SQL Server 2005 and above. Since the OP did not specify which version was being used it should be noted in your solution.

Arav Pradeep Gupta · Answer 3 · 2012-04-23T02:06:00

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

SQL

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
);