is it possible to get dates between the start date and end date without using while loop ..just by using select statement it should return all the dates between startdate and enddate... Any help ....
Jul 19, 2010 at 02:28 AM
Alban Lijo
(comments are locked)
|
using a tally table (starting at n =1)
More on Tally tables here Or if you don't want to use a 'physical' tally table, utilise a cte-based one
no point me adding an option thats the same theory. +1 to you :)
Jul 19, 2010 at 02:54 AM
Fatherjack ♦♦
thank you for the almost requirement which i have got it ...but here what is happening is month and year is incrementing by 1 ...but i need days ...
Jul 19, 2010 at 03:12 AM
Murali
@Murali : can you post the exact code you are using
Jul 19, 2010 at 03:22 AM
Kev Riley ♦♦
DECLARE @N INT SET @N=1 SELECT PRDDTL_STARTDATE + (@N-1) FROM WHERE @N-1 < DATEDIFF (dd, PRDDTL_STARTDATE, PRDDTL_ENDDATE)
Jul 19, 2010 at 03:29 AM
Murali
Jul 19, 2010 at 03:37 AM
Kev Riley ♦♦
(comments are locked)
|
I wanted to add this as a comment to Kev's answer but it was too big. This is the fastest function to create a "virtual" tally table i have ever seen. You can step forward or backward and you can step more than one step at a time. (like when you want each monday in the given range)
Wow, very fast and very usable. Thanks for sharing this one Hakan. :) +1
Jul 19, 2010 at 05:34 AM
Fatherjack ♦♦
The best with this function is that it doesn't consume any IO, only CPU and memory, and it is an inline tabled valued function that doesn't suffer from the drawbacks of scalar valued functions.
Jul 19, 2010 at 11:20 AM
Håkan Winther
(comments are locked)
|
(comments are locked)
|