c# - 如何根据日期和时间从SQL查询到GridView进行排序

c# - 如何根据日期和时间从SQL查询到GridView进行排序,第1张

在页面加载时对此函数进行排序:

protected void Page_Load(object sender, EventArgs e)
{
    ViewState["sortOrder"] = "Desc";
    ViewState["sortExp"] = "Completed Date";
    PD("Completed Date", "Desc", true);
}

public void PD(string sortExp, string sortDir, bool blOnLoad)
{
    string qry = @"Select [status] 'Status', [Completed Date] 'Completed Date' FROM [mydb1].[dbo].[table1]";
    using (SqlConnection conn = new SqlConnection(gloString))
    {
        try
        {
            SqlCommand cmd = new SqlCommand(qry, conn);

            SqlDataAdapter da = new SqlDataAdapter(query, conn);

            myDataSet = new DataSet();
            da.Fill(myDataSet);

            myDataView = new DataView();
            myDataView = myDataSet.Tables[0].DefaultView;

            if (sortExp != string.Empty)
            {
                myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
            }

            yourTasksGV.DataSource = myDataView;
            yourTasksGV.DataBind();
        }
        catch (Exception ex)
        {
            string error = ex.Message;
        }
    }
}
protected void yourTasksGV_Sorting(object sender, GridViewSortEventArgs e)
{
    ViewState["sortExp"] = e.SortExpression;
    PD(e.SortExpression, sortOrder, false);
}
public string sortOrder
{
    get
    {
        if (ViewState["sortOrder"].ToString() == "Desc")
        {
            ViewState["sortOrder"] = "Asc";
        }
        else
        {
            ViewState["sortOrder"] = "Desc";
        }

        return ViewState["sortOrder"].ToString();
    }
    set
    {
        ViewState["sortOrder"] = value;
    }
}

[Completed Date]列属于datetime类型。

RTRIM(Att12) 'Completed Date' = "May 20 2015  2:44PM"
RTRIM(CONVERT(VARCHAR(10), Att12, 101))   ' '   RIGHT(CONVERT(VARCHAR, RTRIM(Att12), 100), 7) 'Completed Date' = "05/20/2015  2:44PM" //this is being used for the bottom result.

上面的代码运行后,结果如下:

Completed Date
06/11/2015 11:39AM                                                      
06/11/2015  3:38PM                                                      
06/11/2015  1:12PM                                                      
06/11/2015  1:11PM                                                      
06/11/2015  1:06PM                                                      
06/11/2015  1:05PM                                                      
06/11/2015  1:04PM                                                      
06/11/2015  1:03PM                                                      
06/10/2015  9:35AM                                                      
06/09/2015 12:29PM                                                      
06/09/2015 12:24PM                                                      
06/09/2015 12:20PM                                                      
06/09/2015  9:40AM                                                      
06/09/2015  9:16AM                                                      
06/09/2015  9:11AM                                                      
06/05/2015 10:33AM                                                      
06/05/2015  8:35AM

正如您所见,11:39AM始终位于顶部。

如何修改脚本以确保正确排序。

最佳答案:

2 个答案:

答案 0 :(得分:1)

尝试使用方括号围绕排序表达式:

ViewState["sortOrder"] = "DESC";
ViewState["sortExp"] = "[Completed Date]";

此外,您的[完成日期]字段应为DateTime数据类型。不要将其转换为字符串以获得所需的格式。您可以使用字符串格式来获取输出所需的格式。因此,您的select语句应返回Att12日期时间字段:

Select [status] 'Status', [Att12] 'Completed Date' FROM [mydb1].[dbo].[table1]"

然后显示您可以使用字符串格式:

DateTime.Now.ToString("dd-MM-yyyy hh:mm tt")

或者您可以格式化网格中的列以显示格式化的日期时间。对于格式参考,您可以结帐Custom Date and Time Format Strings。

答案 1 :(得分:1)

您是否尝试过括号Completed Date

ViewState["sortExp"] = "[Completed Date]";

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复