July 8, 2013 at 6:45 am
Hello,
declare @date1 datetime
declare @date2 datetime
set @date1='2013-08-01'
set @date2='2013-08-07'
now I want the output between @date1 and @date2, how can I do that?
July 8, 2013 at 6:48 am
Do you mean something like this?
SELECT DATEDIFF(dd, @date1, @date2)
July 8, 2013 at 6:50 am
No, that's will give the difference.
I want dates between those two like below
2013-08-01
2013-08-02
2013-08-03
2013-08-04
2013-08-05
2013-08-06
2013-08-07
2013-08-08
July 8, 2013 at 6:58 am
You can do this using a Tally Table
declare @date1 datetime
declare @date2 datetime
set @date1='20130801'
set @date2='20130807'
SELECTDATEADD(DAY,T.N-1,@date1) AS Dates
FROMdbo.Tally AS T
WHERET.N <= DATEDIFF(DAY,@date1,@date2) + 1
For more information on what a Tally table is and how it replaces a loop, check the link below
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 8, 2013 at 7:10 am
Thanks Kingston Dhasian,
any other way?
July 8, 2013 at 7:23 am
Basically is the same method but using a different Tally approach to have zero reads.
declare @date1 datetime
declare @date2 datetime
set @date1='20130801'
set @date2='20130807'
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS ( --=== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATEDIFF(DAY,@date1,@date2)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECTDATEADD(DAY,T.N,@date1) AS Dates
FROMcteTally AS T
July 9, 2013 at 7:44 am
This can also be done with a WHILE statement with the results being put into a table variable. Although I would suggest the other posts first, this is an option.
DECLARE @date1 datetime, @date2 datetime, @tot int, @cnt int, @datetemp datetime
SET @date1 = '2013-08-01'
SET @date2 = '2013-08-07'
SELECT @cnt = 1, @tot = DATEDIFF(dd, @date1, @date2)
DECLARE @dates TABLE
(date datetime)
WHILE @cnt <= @tot
BEGIN
IF @cnt = 1 BEGIN INSERT INTO @dates (date) VALUES (@date1) END
SELECT @datetemp = DATEADD(dd, @cnt, @date1)
INSERT INTO @dates (date) VALUES (@datetemp)
SET @cnt = @cnt + 1
END
SELECT * FROM @dates
Results:
2013-08-01 00:00:00.000
2013-08-02 00:00:00.000
2013-08-03 00:00:00.000
2013-08-04 00:00:00.000
2013-08-05 00:00:00.000
2013-08-06 00:00:00.000
2013-08-07 00:00:00.000
July 9, 2013 at 8:10 am
The tally table is definitely the way to go. Performance is through the roof. If you don't like using it inline because you don't understand it yet, you could encapsulate it into a table-valued function and then call the function from your code, but please consider adding the tally table to your toolkit. Here's Jeff Moden's excellent article on it: http://www.sqlservercentral.com/articles/T-SQL/62867/
July 9, 2013 at 9:23 am
Not sure if this will perform any better - but is another way of generating your tally table and a calendar.
Declare @date1 date = '20130101'
, @date2 date = '20131231';
With cteTally (n)
As (
Select d.n
From (values (0)) As d(n)
Union All
Select d1.n + 1
From cteTally As d1
Where d1.n + 1 <= datediff(day, @date1, @date2)
)
Select *
, dateadd(day, n.n, @date1)
From cteTally n
Option (maxrecursion 0);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2013 at 9:34 am
Jeffrey Williams 3188 (7/9/2013)
Not sure if this will perform any better - but is another way of generating your tally table and a calendar.
You may want to take a look at this article.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply