Count Sundays Between Two Dates

Once day I developed one application, the system required to count number of Sundays between duration of date. I tried for 2 hour to find solution to get number of Sundays. And after I finished my work I want to share this tip to other developer. I wrote the scripts to count, we just input parameter @StartDate and @EndDate, it will show result.

Please run this code and test your self. 

declare @StartDate datetime, @EndDate datetime

set @StartDate = '2009-01-01'

set @EndDate = '2010-06-01'



Select Sundays=Count(*) From (Select Top (Datediff (day, @StartDate, @EndDate) +1)

[Date] = dateadd(day, ROW_NUMBER()

Over(order by c1.name, c2.name), convert(char(10),@StartDate-1,110))

From sys.columns c1

cross join sys.columns c2) x

Where datepart(dw,[Date]) = 1;

-----------------------------------------------------------------------------------------------

Output: Number of sundays between two date is 74 sundays.

How to Show Data In TreeView

It you have some data in table and want to show it in TreeView
You can write code below:
1. tbl_Data have have data below:
2. Then please write code below:
TreeView1.Nodes.Clear();
private void showtreeview()
SqlCommand cmd= new SqlCommand ();
cmd.Connection =conn;
cmd.CommandType=CommandType.Text;
cmd.CommandText = "Select * from tbl_data order by Leve";
SqlDataReader sqlDR = cmd.ExecuteReader();

TreeView1.Nodes.Clear();

int lvCode = 0;

while (sqlDR.Read())
{
TreeNode tn = new TreeNode();
tn.Text = sqlDR["Level"].ToString() + "-" + sqlDR["Text"].ToString();
tn.Value = sqlDR["Level"].ToString();

if (sqlDR["Level"].ToString().Equals("1")){

TreeView1.Nodes.Add(tn);
TreeView1.Nodes[TreeView1.Nodes.Count - 1].Select();

}else
{
if ( lvCode 0)
{
TreeView1.SelectedNode.ChildNodes "+
" [TreeView1.SelectedNode.ChildNodes.Count-1].Select();
}
}
else if (lvCode > int.Parse(sqlDR["Level"].ToString()))
{
TreeView1.SelectedNode.Parent.Select();
}


TreeView1.SelectedNode.ChildNodes.Add(tn);
}
lvCode = int.Parse(sqlDR["Level"].ToString());


}
}

3. When already to finish your code you can run it.

How to Convert Null Value To Number In SQL Server

Do you know, or you can calculate Null value in SQL Server or not?
when you select some data from table of database, sometime you get
null value. but you want to use this value for calculate to other values.
The solution, you must convert null value to numeric value when your
data is null value.

How to Compare Date Time In C#

This article for know about during of start date and end date.
Example: StartDate= 10/05/2009 and EndDate= 15/05/2009
You want to know about during of 2 date. how do you do?
and you want to know which first and which last.
If you want to do it please see code below:

1. Find during of this date.

DateTime startDate= new DateTime();
DateTime endDate=new DateTime();

startDate=Convert.ToDateTime(txtStartDate.Text);
endDate=Convert.ToDateTime(txtEndDate.Text);

int during= endDate-startDate;

Response.Write(during.ToString());

But this code for find during that in the same month and the same year.
can not calculate in different year and month. But we can compare date time
by using function of C#.

2. Compare Date Time

DateTime startDate= new DateTime();
DateTime endDate=new DateTime();

startDate=Convert.ToDateTime(txtStartDate.Text);
endDate=Convert.ToDateTime(txtEndDate.Text);

int during =endDate.CompareTo(startDate);

if (startDate > endDate)
{
Response.Write("Start Date is big");
}
else
{
Response.Write("Start Date is Small");
}

How to Show Value From Select Multi Record In SQL

This code for show each values that you select many record
from sql server.

please see code below:

string str="select Name form tblName where ID between 1 and 5";
Sqlcommand cmd=new Sqlcommand(str,connection);
DataReader Dreader=cmd.ExecuteReader();

While (Dreader.Read())
{
if (DReader["ID"].ToString().Equals("1")) txtName1.Text = DReader["Name"].ToString();
if (DReader["ID"].ToString().Equals("2")) txtName2.Text = DReader["Name"].ToString();
if (DReader["ID"].ToString().Equals("3")) txtName3.Text = DReader["Name"].ToString();
if (DReader["ID"].ToString().Equals("4")) txtName4.Text = DReader["Name"].ToString();
if (DReader["ID"].ToString().Equals("5")) txtName5.Text = DReader["Name"].ToString();

}
Dreader.Close();