Dynamically passing dates between clause

Create PROCEDURE dbo.test
(
@Table nvarchar(50),
@Field nvarchar(50),
@BeginDate DateTime,
@EndDate DateTime
)
AS
DECLARE @Sql nvarchar(1000)
SET @Sql = 'SELECT ' + @Table + '.* FROM ' + @Table + ' WHERE ' + @Field + ' BETWEEN ''' + convert(nvarchar(20), @BeginDate) + ''' AND ''' + convert(nvarchar(20), @EndDate) + ''''
EXEC sp_executesql @Sql

function which will return st, th, nd and rd with date in SQL SERVER

1.create a function as below
create function [dbo].[get_tidy_date]
(
    @date datetime = null
)
returns nvarchar(50)
as begin
     
    declare @d int,
    @m nvarchar(15),
    @y nvarchar(4),
    @end nvarchar(1),
    @return nvarchar(50)
 
    if @date is null
        set @date=getdate()
    select @d=datepart(d, @date), @m=datename(m, @date), @Y=
datename(yyyy,@date), @end=right(convert(nvarchar(2), @d),1)
    set @return=
        convert(nvarchar(2), @d)
        +case
            when @d in(11, 12, 13) then 'th'
            when @end='1' then 'st'
            when @end='2' then 'nd'
            when @end='3' then 'rd'
            else 'th'  
        end
        +' '+@m+' '+@y
    return @return
 
end


2.Call this function as follows:

SELECT dbo.get_tidy_date(GETDATE())

Output is like this
2nd March 2011

Gridview Sorting with up and down arrow + paging

1. Add Gridview and enable sorting and write sortexpression field

      <asp:GridView ID="grdCompany" runat="server" AutoGenerateColumns ="False"
        DataKeyNames="ID,TraceStatus,CaseRefNo" Width="100%"
        onrowdatabound="grdCompany_RowDataBound" AllowPaging="True" AllowSorting="True"
              onrowcreated="grdCompany_RowCreated" onsorting="grdCompany_Sorting"
              onpageindexchanging="grdCompany_PageIndexChanging">
        <Columns>
        <asp:hyperlinkfield headertext="Reference No." datatextfield="CaseRefNo"  SortExpression="CaseRefNo"
                ItemStyle-Width="80px"
                datanavigateurlformatstring="viewcase.aspx?refid={0}&TraceType=C"
                datanavigateurlfields="CaseRefNo" >
            <HeaderStyle ForeColor="White" />
            <ItemStyle Width="80px" />
            </asp:hyperlinkfield>
        <%--<asp:BoundField DataField="CompanyName1" HeaderText="Company Name" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="PropertyName1" HeaderText="Property Name" ItemStyle-Width ="100px" />  --%>
        <asp:TemplateField HeaderText="Company Name/ <br/>Property Name" SortExpression="CompanyName1">
        <ItemTemplate>
          <%#Eval("CompanyName1")%>
          <br />
          <%#Eval("PropertyName1") %>
        </ItemTemplate>
            <HeaderStyle ForeColor="White" />
        </asp:TemplateField> <asp:TemplateField HeaderText="Trace Level<br/>Date of Submission" SortExpression="Tracelevel">
        <ItemTemplate>
          <%#Eval("Tracelevel")%>
          <br />
          <%#Eval("DOS")%>
        </ItemTemplate>
                <HeaderStyle ForeColor="White" />
        </asp:TemplateField>
        <%--<asp:BoundField DataField="Tracelevel" HeaderText="Trace Level" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="DOS" HeaderText="Date of Submission" ItemStyle-Width ="100px" /> 
        <asp:BoundField DataField="TraceStatus" HeaderText="Status" ItemStyle-Width ="100px" />  --%>
        <asp:TemplateField HeaderText="Status" SortExpression="TraceStatus">
        <ItemTemplate>
          <asp:Label ID="lblStatus" runat="server" Text='<% #Eval("TraceStatus") %>' ></asp:Label>
          <asp:HyperLink ID="vwReport" runat="server" Text="View Report"></asp:HyperLink>
        </ItemTemplate>
            <HeaderStyle ForeColor="White" />
        </asp:TemplateField>
        </Columns>
        <HeaderStyle CssClass ="t-heading"   height="35px" /> <RowStyle CssClass ="b-table-text-inside3" height="30px" />
        </asp:GridView>



2. write following code in code behind page.

DataView dv = new DataView();
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";


//Write a method which return view
  private DataView bindgridSort()
    {
        clsCase objCase = new clsCase();
        objCase.pClientId = clsSessions.sesUserID;
        objCase.pTraceType = "C";
        DataSet dsCase = new DataSet();
        dsCase = objCase.GetAllCases();

        if (dsCase.Tables[0].Rows.Count > 0)
        {
            if (ViewState["sortExpr"] != null)
            {
                dv = new DataView(dsCase.Tables[0]);
                dv.Sort = (string)ViewState["sortExpr"];
            }
            else
                dv = dsCase.Tables[0].DefaultView;
        }
        else
        {
            lblMsg.Text = "No Details Found.";
        }
        return dv;

// Add row_created events

protected void grdCompany_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            int sortColumnIndex = GetSortColumnIndex();
            if (sortColumnIndex != -1)
            {
                AddSortImage(sortColumnIndex, e.Row);
            }
        }
    }
// Add sorting event
 protected void grdCompany_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortExpression = e.SortExpression;
        ViewState["SortExpression"] = sortExpression;
        if (GridViewSortDirection == SortDirection.Ascending)
        {
            GridViewSortDirection = SortDirection.Descending;
            SortGridView(sortExpression, DESCENDING);
        }
        else
        {
            GridViewSortDirection = SortDirection.Ascending;
            SortGridView(sortExpression, ASCENDING);
        }
    }


 private int GetSortColumnIndex()
    {
        // Iterate through the Columns collection to determine the index
        // of the column being sorted.
        foreach (DataControlField field in grdCompany.Columns)
        {
            if (field.SortExpression == (string)ViewState["SortExpression"])
            {
                return grdCompany.Columns.IndexOf(field);
            }
        }
        return -1;
    }
    private void AddSortImage(int columnIndex, GridViewRow headerRow)
    {
        // Create the sorting image based on the sort direction.
        Image sortImage = new Image();
        if (GridViewSortDirection == SortDirection.Ascending)
        {
            sortImage.ImageUrl = "Images/uparrow.gif";
            sortImage.AlternateText = "Ascending Order";
            sortImage.ImageAlign = ImageAlign.Top;
            sortImage.ImageAlign = ImageAlign.Middle;
        }
        else
        {
            sortImage.ImageUrl = "Images/downarrow.gif";
            sortImage.ImageAlign = ImageAlign.AbsBottom;
            sortImage.ImageAlign = ImageAlign.Middle;
            sortImage.AlternateText = "Descending Order";
        }
        // Add the image to the appropriate header cell.
        // headerRow.Cells[columnIndex].Controls.Add(space);
        headerRow.Cells[columnIndex].Controls.Add(sortImage);
    }
    private SortDirection GridViewSortDirection
    {
        get
        {
            if (ViewState["sortDirection"] == null)
                ViewState["sortDirection"] = SortDirection.Ascending;
            return (SortDirection)ViewState["sortDirection"];
        }
        set { ViewState["sortDirection"] = value; }
    }
    private void SortGridView(string sortExpression, string direction)
    {
        DataView dv = bindgridSort();
        dv.Sort = sortExpression + direction;
        grdCompany.DataSource = dv;
        grdCompany.DataBind();
    }
    protected void grdCompany_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grdCompany.PageIndex = e.NewPageIndex;
        BindCaseDetails("C");
    }