Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
	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 22:07pm
Edited 8-May-13 22:31pm
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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!
Smile | :)
  Permalink  
v2
Comments
sreeharshakakarla at 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 at 9-May-13 4:24am
   
Welcome!
Glad to help you! :)
Aarti Meswania at 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()
sreeharshakakarla at 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 at 10-May-13 0:39am
   
check column name in "MyDataTable"
sreeharshakakarla at 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 at 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]
Awesomeprogramming at 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 at 10-May-13 2:54am
   
use ddprojectid.SelectedValue; instead of ddprojectid.SelectedItem.Value;
Awesomeprogramming at 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 at 10-May-13 3:27am
   
Most welcome!
Glad to know about "new thing you learn today"
Always keep learning. Good luck :)
Aarti Meswania at 10-May-13 2:59am
   
visit...
http://stackoverflow.com/questions/4801831/how-to-get-the-previous-item-on-dropdownlist-before-onselectedindexchanged-fires
Rohan Leuva at 9-May-13 3:47am
   
↑ voted.It needs to be.
Aarti Meswania at 9-May-13 4:24am
   
Thank you! :)

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

  Print Answers RSS
0 DamithSL 265
1 CPallini 235
2 OriginalGriff 233
3 Maciej Los 195
4 George Jonsson 170
0 OriginalGriff 5,305
1 DamithSL 4,382
2 Maciej Los 3,760
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,901


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 9 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100