如何根据日期和时间从 sql 查询到 GridView 进行排序
本文关键字:查询 GridView 排序 sql 何根 日期 时间 | 更新日期: 2023-09-27 17:55:52
在
页面加载时像这样对函数进行排序:
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
总是卡在顶部。
如何修改脚本以确保其正确排序。
尝试在排序表达式两边加上方括号:
ViewState["sortOrder"] = "DESC";
ViewState["sortExp"] = "[Completed Date]";
此外,您的 [完成日期] 字段应为日期时间数据类型。不要将其转换为字符串以获取所需的格式。您可以使用字符串格式来获取任何所需的输出格式。因此,您的 select 语句应返回Att12
日期时间字段:
Select [status] 'Status', [Att12] 'Completed Date' FROM [mydb1].[dbo].[table1]"
然后,对于显示,您可以使用字符串格式:
DateTime.Now.ToString("dd-MM-yyyy hh:mm tt")
或者,您可以设置网格中列的格式,以显示该格式的日期时间。有关格式参考,您可以查看自定义日期和时间格式字符串。
您是否尝试过在Completed Date
周围放置括号?
ViewState["sortExp"] = "[Completed Date]";