Saturday, May 2, 2009

Displaye Dates on which particular day Falles Between two dates.

If we want that between two dates on which dates particular day(Monday,tuesday) falls.
we can write Code both in C# and Sql server.
eg.
between two dates..LeftDate='2009-04-02' and RightDate='2009-04-02'
Monday Falls On dates. 2009-04-05 , 2009-04-12 , 2009-04-19 .

To find out this we will..

Insert date range in some table.perform looping on table rows then looping between dates.
and insert Date on which monday falls in New table. than select distinct among that table.


(1)Code In c#.

protected void Page_Load(object sender, EventArgs e)
{
Gridview1.DataSource = GetDatatable();
Gridview1.DataBind();
DataTable dt = GetDatatable();
DataTable dtNew = new DataTable();
dtNew.Columns.Add("MondayDates", typeof(DateTime));
dt.Columns.Add("MondayDates", typeof(DateTime));
foreach (DataRow dr in dt.Rows)
{
DateTime dt1 = Convert.ToDateTime(dr["Leftdates"]);
DateTime dt2 = Convert.ToDateTime(dr["Rightdates"]);
while (dt1 <= dt2) { if (dt1.DayOfWeek == DayOfWeek.Monday) { dtNew.Rows.Add(dt1); } dt1 = dt1.AddDays(1); } } Gridview1.DataSource = dtNew.DefaultView.ToTable("DistinctTable", true, "MondayDates"); Gridview1.DataBind(); } private DataTable GetDatatable() { DataTable dt = new DataTable(); dt.Columns.Add("Leftdates", typeof(DateTime)); dt.Columns.Add("Rightdates", typeof(DateTime)); dt.Rows.Add("4/1/2009", "4/28/2009"); return dt; }



(2)Code in SQL server.

Using Cusror in Sql server we can perform looping in sql server.

declare @tmpTable TABLE(LeftDates datetime,RightDates datetime)
declare @mondayTable TABLE(MondayDate datetime)
insert into @tmpTable values('2009-04-02','2009-04-20')
insert into @tmpTable values('2009-04-02','2009-04-20')
--Select * from @tmpTable
Declare myCursor Cursor for Select LeftDates,RightDates from @tmpTable
open myCursor
declare @LeftDate as Datetime
declare @RightDate as Datetime
declare @dateDay int
fetch next from myCursor Into @LeftDate,@RightDate
while(@@fetch_status=0)
begin
fetch next from myCursor Into @LeftDate,@RightDate
declare @tmpLeftDate datetime
set @tmpLeftDate=@LeftDate
WHILE @tmpLeftDate < @RightDate BEGIN SET @tmpLeftDate = @tmpLeftDate + 1 select @dateDay=DATEPART(w,@tmpLeftDate) if(@dateDay=1)--Day Number of Monday =1 insert into @mondayTable values(@tmpLeftDate) END end close myCursor deallocate myCursor select distinct MondayDate from @mondayTable


thats all.

thanks.