List of Months and years between two dates

Question

3.40/5 (3 votes)

See more:

Hello all,

I have a situation in which I need to display list of months with years between two dates saved in database i.e BeginDate and Enddate. I researched and found a SQL function that displays months between two dates but I have no idea about obtaining months/year at same time.
Ex: Begin Date(MM/YY/YEAR): 03/02/2012 EndDate : 10/01/2012

I should get output as March/2012,April/2012,May/2012 ....October/2012. or it can be 03/2012,04/2012,05/2012...10/2012. Ultimately I would like to display this output in a dropdown dynamically.

Here is the function I have modified that displays only months i.e March,April,May...October for above dates. I would appreciate if you could help me with this regard.

SQL

	ALTER PROCEDURE dbo.Addmonthslist
	
	@BeginDate AS Datetime,
	@EndDate AS Datetime,
	@ProjectID AS varchar(50)
AS
SELECT
 BeginDate,
 EndDate
 from tblProjects where ProjectID = @ProjectID
 

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @BeginDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'
AND     x.number <= DATEDIFF(MONTH, @BeginDate, @EndDate);

Posted 8-May-13 21:07pm

Slacker89

Updated 8-May-13 21:31pm

v3

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-05-08T21:15:00

Solution 1

SQL

ALTER PROCEDURE dbo.Addmonthslist
	
	@BeginDate AS Datetime,
	@EndDate AS Datetime,
	@ProjectID AS varchar(50)
AS
SELECT
 
 BeginDate,
 EndDate
 from tblProjects where ProjectID = @ProjectID
 
 
SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @BeginDate)) + '-' + Convert(varchar(4), Year(DATEADD(MONTH, x.number, @BeginDate))) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'
AND     x.number <= DATEDIFF(MONTH, @BeginDate, @EndDate);
	
	RETURN

Happy Coding!
:)

Posted 8-May-13 21:15pm

Aarti Meswania

v2

Comments

Slacker89 9-May-13 3:38am

Thanks for your quick solution. It works like charm . You are awesome. How should I display this result in a dropdown. I tried using SQL dataadapter but it gave me an error since I am not declaring the output result anywhere. Please help me :(

Aarti Meswania 9-May-13 4:24am

Welcome!
Glad to help you! :)

Aarti Meswania 9-May-13 4:35am

take output in a datatable
and then assign datatable to dropdown's datasource property
and dropdown.DisplayMember="ColumnName" e.g. "MonthName" is your column name here
then Dropdown.Bind()

Slacker89 9-May-13 19:13pm

I tried as you mentioned but I cannot get Displaymember as a property for dropdown. Here is my code, I used datatext and datavalue property( I know its wrong) since displaymember is not working. Please have a look and correct it. Appreciate your time and patience.
Error I got "DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'MonthName'."
private void Monthdata()
{
var str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var conn = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("Addmonthslist", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProjectID", SqlDbType.VarChar).Value = ddprojectid.SelectedItem.Value;
cmd.Parameters.Add("@BeginDate", SqlDbType.VarChar).Value = ddbegin.Text;
cmd.Parameters.Add("@EndDate", SqlDbType.VarChar).Value = ddend.Text;
var adap = new SqlDataAdapter(cmd);
DataTable myDataTable = new DataTable();
adap.Fill(myDataTable);
ddmonth.DataSource = myDataTable;
ddmonth.DataTextField = "MonthName";
ddmonth.DataValueField = "MonthName";
ddmonth.DataBind();
}

Aarti Meswania 10-May-13 0:39am

check column name in "MyDataTable"

Slacker89 10-May-13 1:16am

My dumbness, Output of the SQL function wrote above i.e MonthName is not a column of the table. Table has only BeginDate, EndDate and ProjectID. That function takes these input and gives me output in the name of MonthName. How do I store "MonthName" result in a table? Is there any alternative for this problem? I am really sorry for confusing you.

Aarti Meswania 10-May-13 1:22am

There is not any problem, you are getting two tables as result of store procedure...
from data-adapter take data in dataset

now in dataset you will see two tables
so,
assign drpdown.datasource = ds.Tables[1]

Slacker89 10-May-13 2:26am

That works with a small bug. For Ex: I select a Projectid for which BeginDate = 10/1/2012 and EndDate = 6/1/2013. It is showing the value of previous projectID calculated " MonthName" instead of showing for current selection. Once I select next projectid, previous rows MonthNames are pulled. Why is this happening? Stored Procedure works fine in SQL Server. Here is my code

private void Monthdata()
{
var str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
var conn = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("Addmonthslist", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProjectID", SqlDbType.VarChar).Value = ddprojectid.SelectedItem.Value;
cmd.Parameters.Add("@BeginDate", SqlDbType.VarChar).Value = tbbegin.Text;
cmd.Parameters.Add("@EndDate", SqlDbType.VarChar).Value = tbending.Text;
var adap = new SqlDataAdapter(cmd);
var ds = new DataSet();
adap.Fill(ds);
ddmonth.DataSource = ds.Tables[1];
ddmonth.DataTextField = "MonthName";
ddmonth.DataValueField = "MonthName";
ddmonth.DataBind();

}
protected void ddprojectid_SelectedIndexChanged(object sender, EventArgs e)
{
Monthdata(); // Populating above MonthData event on selection of ProjectID

var str = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string qry = "select CONVERT(VARCHAR(8), BeginDate, 1) AS BeginDate, CONVERT(VARCHAR(8), EndDate, 1) AS EndDate from tblProjects where ProjectID ='" + ddprojectid.SelectedItem.Value + "'";
SqlConnection conn = new SqlConnection(str);

SqlCommand cmd = new SqlCommand(qry, conn);
SqlDataReader sdr;

try
{

if (conn.State != ConnectionState.Open)
conn.Open();

sdr = cmd.ExecuteReader();
while (sdr.Read())
{
tbbegin.Text = sdr["BeginDate"].ToString();
tbending.Text = sdr["EndDate"].ToString();

}

}

catch (Exception ex)
{

lblError.Text = ex.ToString();

}

finally
{

conn.Close();

}

}

Aarti Meswania 10-May-13 2:54am

use ddprojectid.SelectedValue; instead of ddprojectid.SelectedItem.Value;

Slacker89 10-May-13 3:18am

Bingo!!! I found the culprit :P I have to load event Monthdata at the end instead of first in ddprojectid_selectedindexchanged event. May be its taking null at first and then passing a value less than current selection to stored procedure. I found it using a label to display projectid selection before adapter fills in Monthdata().
i.e (In ddprojectid_SelectedIndexChanged event)I changed this
finally
{
Monthdata()
conn.Close();
}
Thank you very much for your valuable support. I have learnt something new today. Appreciate it Aarti :)

Aarti Meswania 10-May-13 3:27am

Most welcome!
Glad to know about "new thing you learn today"
Always keep learning. Good luck :)

Aarti Meswania 10-May-13 2:59am

visit...
http://stackoverflow.com/questions/4801831/how-to-get-the-previous-item-on-dropdownlist-before-onselectedindexchanged-fires

Thanks7872 9-May-13 3:47am

↑ voted.It needs to be.

Aarti Meswania 9-May-13 4:24am

Thank you! :)

List of Months and years between two dates

1 solution

Solution 1

Add your solution here

Preview 0

List of Months and years between two dates

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us