排序访问数据（日期时间格式错误）

Question

排序访问数据（日期时间格式错误）

c#ms-access

3

问题：

我的表中有一些字段的日期格式不正确，它们是使用C#中的.ToString()方法格式化的，但我打错了一个字母，输入成了这样：MM/dd/yyyy hh:mm/ss tt

现在，如果你仔细看，你会发现MM/dd/yyyy hh:mm----> '/' <---ss

应该是':'

然后我在我的C#代码中进行了修复（也就是说，一些值被格式化为：MM/dd/yyyy hh:mm:ss tt），

问题是，我正在尝试按日期顺序从表中选择值（我不能只按'Datefieldnamehere'排序），因为一些字段的格式不正确，其中包含'/'。

我尝试过的方法：

现在，我想到了一个办法，因为时间总是在同一个位置（这对我来说很重要），所以我可以只取时间中的数字子字符串并将它们排序；首先按上午，然后按下午排序。

我可以使用 MID([ColumnName],11,2)、MID([ColumnName],14,2)、MID([ColumnName],16,2) 进行排序，因为无论日期的分隔符如何，每个日期的格式始终为 M/dd/yyyy hh/mm/ss tt。

所以我尝试了以下方法：

(
SELECT SN, StatusCode, Time, Mid(Time,14,2) + ':'+ Mid(Time,17,2) AS TTI
FROM OrderStatus
WHERE StatusCode = 'Finished' and Left(Time,10) = '4/20/2012'
AND Time LIKE '*AM'
ORDER BY Val(Mid([Time],11,2)) DESC
)
UNION ALL (
SELECT SN, StatusCode, Time, Mid(Time,14,2) + ':'+ Mid(Time,17,2) AS TTI
FROM OrderStatus
WHERE StatusCode = 'Finished' and Left(Time,10) = '4/20/2012'
AND Time LIKE '*PM'
ORDER BY Val(Mid([Time],11,2)) DESC
);

只是想看看是否按小时排序，但它并没有这样做，它给了我这个： Err

如您所见，在小时字段中，它按 01 然后是 02，然后又回到了 01...？

无论如何，有一个类似的问题here我已经放了赏金，如果您回答这个问题或那个问题，您将获得赏金，这对其他场景也很有用。

编辑：我真正想要回答的问题是如何按字符串的子字符串进行排序；对于我晚些编辑很抱歉，我发誓我已经改过了，比如说我想通过任何字符串/日期/任何内容的单个数字进行排序，我想知道如何做到： Order By Val(Mid(ColumnName,StartPos,EndPos))

请注意：Order By 给出完全错误的结果。

- user1200540

是的，但也不完全是这样...因为有21000多条记录，我不能简单地调用.replace，因为日期中有/，早期分割年月日，而且我不能在子字符串的确切位置替换值，因为有些日期在月份中有10/11/12（一个额外的数字），我想我可以做一个if(date.length == 22)和21等来适应它，但这真的违背了这个问题未来使用的目的。我真正想知道的是如何按列中字符串的子字符串排序，如果可能的话... - user1200540

这是一个非常好的建议，我会投赞成票。 - user1200540

我认为修复问题总比找到一种复杂的方法来完成工作更好，而不必修复它。由于这将是一次性的事情，您可以使用一个临时字段用于日期和一个用于时间以拆分所涉及的字段。然后，简单的替换应该很容易。（尽管我看到它没有回答按子字符串排序的问题..;)) - user783388

3个回答

2

将日期字段更改为实际的日期字段，而不是字符串。使用两个更新将所有值迁移到新字段 - 一个选择旧格式的所有行，另一个选择新格式的所有行。删除日期的字符串列，享受正确类型的数据。

如果您真的想将其保留为字符串，请确保使用标准的ISO8601格式（大约为YYYY-MM-DDTHH:MM.ssss），以确保排序/本地化的安全性。

- Alexei Levenkov

有8000多行代码不是我写的，以字符串形式访问这些数据在C#中，我不能简单地将其更改为日期时间，否则我早就已经这样做了。 - user1200540

我投了你的答案一票，因为我很感激你的关注并且你是非常正确的（它应该是一个真正的日期，而不是一个字符串），但这并没有回答我的问题。 - user1200540

1

谢谢。如果您最终要修改此代码，我仍然建议您切换到可排序的字符串格式。HansUp的转换看起来正是您需要的。 - Alexei Levenkov

1

我没有表格可以测试，但我想提供我的想法。
我将使用Left、Mid和Right函数以及CDate和CInt函数。
我将获取时间字段（16个字节）的最左侧部分，添加“：00”作为秒数和PM/AM指示器。这个字符串可以使用CDate转换为日期。
然后我将获取秒数部分并将它们转换为整数。
现在可以通过按第一部分（TTI）和第二部分（TTS）的顺序进行排序来达到目标，而不需要使用UNION。
但是有一个问题，有些日期只包含1个字符的月份（例如：4月对12月），幸运的是，我们可以使用IIF运算符选择正确的中间、右侧和左侧数字。

编辑：

SELECT 
SN, StatusCode, Time, 
IIF(Len(Time) = 22, Left(Time,16) + ':00' + Right(Time,2), Left(Time,15) + ':00' + Right(Time,2)) AS TTI, 
IIF(Len(Time) = 22, Mid(Time, 18,2), Mid(Time, 17,2)) as TTS
FROM OrderStatus 
WHERE StatusCode = 'Finished' and Left(Time,10) = '4/20/2012' 
ORDER BY CDate(IIf(Len([Time])=22,Left([Time],16)+ ':00 ' + Right([Time],2),Left([Time],15)+ ':00 ' + Right([Time],2))) DESC , 
         CInt(IIf(Len([Time])=22,CInt(Mid([Time],18,2)),CInt(Mid([Time],17,2)))) DESC;

- Steve

这是错误的使用左和右吧？左边有两个参数，而不是三个。 - user1200540

“Length”和“Val”可以互换使用吗？它无法识别。 - user1200540

@JustinKirk，噢，又出错了。函数应该是Len。抱歉，我正在做一个C#项目，有时候容易混淆 :) - Steve

这似乎是过度处理，仅按列中的单个数字排序... 如果没有人找到更好的解决方案，我会选择它。 - user1200540

@JustinKirk 这真是一个糟糕的情况。如果约束条件允许，最好尝试使用全局更新来纠正这种情况。 - Steve

显示剩余4条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HansUp · Accepted Answer

我的直觉是创建一个函数，并在Access UPDATE语句中运行一次，将存储的值转换为所需格式。

下面是一个快速且简单的函数，可以处理单个和两位数月份。它需要Access 2000或更高版本。

? FixTimeData("4/20/2012 01:34/09 PM")
4/20/2012 01:34:09 PM
? FixTimeData("12/20/2012 01:34/09 PM")
12/20/2012 01:34:09 PM

Public Function FixTimeData(ByVal pIn As String) As String
    Dim astrPieces() As String
    Dim strOut As String
    astrPieces = Split(pIn, " ")
    strOut = astrPieces(0) & " " & _
        Replace(astrPieces(1), "/", ":") & " " & _
        astrPieces(2)
    FixTimeData = strOut
End Function

然后执行类似于以下的 UPDATE 语句...

UPDATE OrderStatus
SET time_field = FixTimeData(time_field)
WHERE time_field Like "*/*/*/*";

如果您使用ADO而不是DAO运行该语句，请在WHERE子句中更改为ANSI通配符字符。

WHERE time_field Like "%/%/%/%";

如果您希望相同的语句可以在ADO或DAO下使用，可以使用ALike与ANSI通配符。

WHERE time_field ALike "%/%/%/%";

我使用了time_field作为字段名称，而你使用了Time，因为Time是一个保留字。如果你不能更改字段名称，在查询中用方括号括起来。 编辑：我的意图是修复数据，让你可以可靠地基于Right(time_field, 11)进行排序。 编辑2：要基于“上部”分钟数字排序，请看看是否可以建立在此方法的基础上，该方法假定你已经修正了存储的日期/时间字符串：

? Left(Format(Minute("4/23/2012 04:02:40 PM"), "00"), 1)
0
? Left(Format(Minute("4/23/2012 04:12:40 PM"), "00"), 1)
1
? Left(Format(Minute("4/23/2012 04:22:40 PM"), "00"), 1)
2

尽管 Minute()函数可以接受字符串，但在将其提供给 Minute()之前，我更愿意使用CDate()显式地将字符串转换为日期/时间。

因此...如果我没有完全走错路...尝试在这样的查询中使用该方法：

SELECT
    Left(Format(Minute(CDate(time_field)), "00"), 1) AS upper_minute,
    OrderStatus. *
FROM OrderStatus
ORDER BY 1;