May 25, 2010 at 10:34 pm
Dear all,
How to Display the dates between the given two dates.
Note : try to display dates without using while or any other loops
eg: given dates are 01-May-2010 and 10-May-2010
OutPut as
01-May-2010
02-May-2010
..
..
08-May-2010
09-May-2010
10-May-2010
Please reply
May 25, 2010 at 11:19 pm
Hi Mohan , the following code wil help you out!
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
-- code to dispaly the dates between 2 dates
declare @startdate datetime
declare @enddate datetime
set @startdate = '01-May-2010'
set @enddate = '10-May-2010'
select dateadd(dd,N-1,@startdate)
from dbo.tally
where n <= datediff(dd,@startdate,@enddate)+1
Hope this helps you!
May 26, 2010 at 12:57 am
Nice approach cold coffee, a bit change in the statements. It’s should like
select dateadd(dd,N-1,@startdate)
from dbo.tally
where n <= datediff(dd,@startdate,@enddate)+1
May 26, 2010 at 2:14 am
arun.sas (5/26/2010)
Nice approach cold coffee, a bit change in the statements. It’s should like
select dateadd(dd,N-1,@startdate)from dbo.tally
where n <= datediff(dd,@startdate,@enddate)+1
Oh yeah, arun, sorry for the mistake!
Mohan u can take this! I will edit my post as well with the change
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply