Missing Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Missing Date

Author

Topic

satheesh
Posting Yak Master

152 Posts

Posted - 2011-07-29 : 09:37:51

Dear All,

I have 'datecreated' column in cst tabel and i need to find the missing date between the given range

where (cst.datecreated between '2011-07-01 00:00:00.000' AND '2011-07-12 23:59:59.997')

Date exist in table
2011-07-01
2011-07-02
2011-07-05
2011-07-08
2011-07-09
2011-07-12

Result (missing date)

2011-07-03
2011-07-04
2011-07-06
2011-07-07
2011-07-10
2011-07-11

Any help regarding this is highly appreciated.Many Thanks.

Regards,
SG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 09:45:42

Easiest perhaps is to use a calendar table or numbers table. You can create one on the fly if you don't have a numbers table. For example:

CREATE TABLE #tmpCalendarTable(dt DATETIME);

DECLARE @startdate DATETIME, @days INT;
SET @startdate = '20110701';
SET @days = 30;

WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < @days)
INSERT INTO #tmpCalendarTable SELECT DATEADD(dd,n-1,@startdate) FROM N;

Once you have the calendar table, you can do a left join:

SELECT 
	dt
FROM
	#tmpCalendarTable t
	LEFT JOIN YourTable y ON y.Dt = t.dt
WHERE
	y.dt IS NULL;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-29 : 10:00:34

Same class as this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163495

Jim

Everyday I learn something that somebody else already knew

derach2000
Starting Member

37 Posts

Posted - 2011-07-29 : 10:14:13

Hi,

I found this solution(http://www.c-sharpcorner.com/Blogs/692/),
and modified it a little bit to get the distinct date parts
of your date time column

DECLARE @StartDate DATETIME,@EndDate DATETIME

SELECT @StartDate = '2011-07-01 00:00:00.000' ,@EndDate = '2011-07-12 23:59:59.997'

select a
from (
SELECT DATEADD(day, z.num, @StartDate) a
FROM (SELECT b10.i + b9.i + b8.i + b7.i + b6.i + b5.i + b4.i + b3.i + b2.i + b1.i + b0.i num
FROM (SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN (SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN (SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN (SELECT 0 i UNION ALL SELECT 256) b8
CROSS JOIN (SELECT 0 i UNION ALL SELECT 512) b9
CROSS JOIN (SELECT 0 i UNION ALL SELECT 1024) b10) z
WHERE z.num <= DATEDIFF(day, @StartDate, @EndDate) )t
where t.a not in
(SELECT distinct DATEADD(Day, 0, DATEDIFF(Day, 0, cst.datecreated))
from cst
where cst.datecreated between @STARTDATE and @ENDDATE)
ORDER BY t.a

Hope this help
Kind regards
Armin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources