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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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! :)