ADODB RecordSet 排序日期字段不正确 [英] ADODB RecordSet sorting date field incorrectly

问题描述

我有一个 ADODB.RecordSet，它是通过调用 ADODB.RecordSet.Open 传递一个 存储过程 和 的名称来填充的代码>连接

Dim results As New ADODB.RecordSetresults.Open usp_GetSomeDate, connection//... 省略

存储过程返回一个名为 SomeDate 的日期作为 DateTime 并正确且成功地填充记录集.

ALTER 过程 usp_GetSomeDate作为从 foo 中选择 SomeDate

然后代码采用这个填充的 RecordSet 并调用 Sort 例如

results.Sort = "SomeDate"

问题是这种排序似乎是按Day而不是整个DateTime对SomeDate进行排序！例如，如果我有以下内容:

04/07/2018 12:43:2301/03/2017 06:32:1208/04/2018 09:23:23

并按升序对结果进行排序，我将得到:

01/03/2017 06:32:1204/07/2018 12:43:2308/04/2018 09:23:23

而我的预期和期望的结果是:

01/03/2017 06:32:1208/04/2018 09:23:2304/07/2018 12:43:23

我不明白为什么代码会将这些排序为 DateTime 以外的任何内容，因为我确保从 SQL 存储过程返回的值是 DateTime - 这通常会导致问题，请查看其他答案(其中数量不多)

我如何更改它以确保排序为 DateTime?

解决方案

正如@Theo 指出的，RecordSet 上的 CursorLocation 属性需要设置为 adUseClient

Dim results As New ADODB.RecordSet结果.CursorLocation = adUseClientresults.Open usp_GetSomeDate, connection//... 省略

此外，确保从 SQL 中选择的字段是 Date 或 DateTime 很重要，以便记录集按日期而不是字符串排序！

I have an ADODB.RecordSet which is populated by calling ADODB.RecordSet.Open passing through the name of a stored procedure and connection

Dim results As New ADODB.RecordSet
results.Open usp_GetSomeDate, connection //... omitted

The stored procedure returns a date called SomeDate as a DateTime and does populate the recordset correctly and successfully.

ALTER Procedure usp_GetSomeDate
AS    
select SomeDate from foo

The code then takes this populated RecordSet and calls Sort e.g.

results.Sort = "SomeDate"

The problem is this sort then appears to sort SomeDate by Day rather than the whole DateTime! for example, if I had the following:

04/07/2018 12:43:23
01/03/2017 06:32:12
08/04/2018 09:23:23

and sorted the results in ascending order, I will end up with:

01/03/2017 06:32:12
04/07/2018 12:43:23
08/04/2018 09:23:23

whereas my expected and desired result would have been:

01/03/2017 06:32:12
08/04/2018 09:23:23
04/07/2018 12:43:23

I can't see why the code would be sorting these as anything other than a DateTime as I am ensuring my value returned from the SQL stored procedure is a DateTime - this is what often causes the problem, looking around at other answers (of which there are not many)

How can I alter this to ensure the sort is as a DateTime?

解决方案

As @Theo pointed out, the CursorLocation property on the RecordSet needs to be set to adUseClient

Dim results As New ADODB.RecordSet
results.CursorLocation = adUseClient
results.Open usp_GetSomeDate, connection //... omitted

Additionally, it is important to make sure the field selected from SQL is a Date or DateTime so that the recordset sorts on the date rather than a string!

这篇关于ADODB RecordSet 排序日期字段不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！