Calculating a T-SQL Query Parameter at Load Time

Question

I'm trying to SELECT records out of an Events Database (with an EventDate field as Date) that have a EventDate that is in the current month.

I have functions for calculating the first day of the month:

public string GetFirstofMonth(DateTime dt)
{
 int thisMonth = dt.Month;
 int thisYear = dt.Year;
 string firstOfMonth = thisMonth.ToString() + "/1/" + thisYear.ToString();
 return firstOfMonth;
}

and the last day of the month:

public string GetLastofMonth(DateTime dt)
{
  DateTime Date2Obj = new DateTime(dt.Year, dt.Month, 1);
  int thisMonth = dt.Month;
  int thisYear = dt.Year;
  Date2Obj.AddMonths(1);
  Date2Obj.AddDays(-1);
  int lastDay = Date2Obj.Day;
  string LastOfMonth = thisMonth + "/" + lastDay + "/" + thisYear;
  return LastOfMonth;
}

So in the MasterPage (or the instance, doesn't matter) I have this SQLDataSource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE (([EventDate] &gt;= @EventDate) AND ([EventDate] &lt;= @EventDate2))">
 <SelectParameters>
   <asp:Parameter DbType="Date" Name="EventDate" />
   <asp:Parameter DbType="Date" Name="EventDate2" />
 </SelectParameters>
</asp:SqlDataSource>

How do I modify the EventDate and EventDate2 parameters in the SQLDataSource to use GetFirstofMonth(DateTime.Today); and GetLastofMonth(DateTime.Today), respectively, as their values? Or is this not possible?

Answer 1

This can be done by handling the SqlDataSource's "Selecting" event as follows:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnSelecting="SqlDataSource1_Selecting"
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE [EventDate] BETWEEN @EventDate AND @EventDate2">
 <SelectParameters>
   <asp:Parameter Name="EventDate" />
   <asp:Parameter Name="EventDate2" />
 </SelectParameters>
</asp:SqlDataSource>

In your codebehind or in your inline code (where ever you have your functions defined) add this method:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceEventArgs e)
{
  e.Command.Parameters["@EventDate"].Value 
           = GetFirstofMonth(DateTime.Now); //replace with your date param
  e.Command.Parameters["@EventDate2"].Value 
              = GetLastofMonth(DateTime.Now); //replace with your date param
}

Answer 2

One way to do this is to put hidden fields on your page, set the value of those fields in code-behind with the dates using your methods, then tie the data source parameters to the hidden fields.

Codebehind

public void Page_Load( ... )
{
     ...
     EventDate.Value = GetFirstOfMonth( DateTime.Today );
     EventDate2.Value = GetLastOfMonth( DateTime.Today );
     ...
}

Mark up

<asp:HiddenField runat="server" id="EventDate" />
<asp:HiddenField runat="server" id="EventDate2" />

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE (([EventDate] &gt;= @EventDate) AND ([EventDate] &lt;= @EventDate2))">
 <SelectParameters>
   <asp:ControlParameter DbType="Date" Name="EventDate" ControlID="EventDate" PropertyName="Value" />
   <asp:ControlParameter DbType="Date" Name="EventDate2" ControlID="EventDate2" PropertyName="Value" />
 </SelectParameters>
</asp:SqlDataSource>

Answer 3

I usually find it simpler to do this:

select * from Events where Month(EventDate) = Month(getdate())

If the month can be selected by the user, then you just substitute a parameter for the month number (returned from a selection list).

Ron

P.S. - If you want to do more complex summary analysis of your event data (counts per hour, day etc.) and you are using SQL Server 2005+ then the function described in this post might be useful.