Question

<<>Problem>:

我的表格中的一些领域有错的格式,使用日期重新编排。 C#中的方法,但i 做了打字,如:MM/dd/yyyh:mm/ss tt。

现在,如果你仔细看一下:MM/dd/yyyy hh:mm ->/ <-- ss

∗∗∗∗∗

在此之前,我先在我的正文法典中加以规定(一些价值观的形式是:MM/dd/yyyh:mm:s tt)

问题在于,试图从表格中选择按日期顺序的数值(即只能按日期划分),因为有些领域采用错误的形式,有/有

What i have Tried:

现在,一幅数字表示,因为时间总是在同一地点(而且对我来说是什么事项),即只能按时间顺序排列编号;一是AM,然后是PM。

我可以命令MID([ColumnName],11,2)、MID([ColumnName],14,2)、MID([ColumnName],16,2)因为无论哪一个日期都总是采用M/dd/yyyy hh/mm/s tt的形式。

So I Tried:

(
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
);

Just to see if that would Order it by hour but it doesn t it gives me this : Err

你们可以看到(在实地)01时,02时,然后回头看......?

https://stackoverflow.com/questions/9411827/ordering-a-union-query-in-ms-access-sql” 如果你回答这个问题或回答这个问题,那么你就会感到羞耻,这也有助于了解其他情况。

Edit: The Question that i really want answered how to sort by a Substring of a string; sorry for the late edit i could of swore i changed this already, say i want to sort by a single digit of any string / date / anything i was wondering how i can do something like: Order By Val(Mid(ColumnName,StartPos,EndPos))

<>说明: 该法令结果是完全错误的。

Answer 1

I don t have a table to test, however I want to give my idea.
I will use the Left, Mid and Right function together with CDate and CInt functions.
I will get the leftmost part of Time Field (16 bytes), add a ":00" for the seconds and the PM/AM designator. This string can be converted to a date using CDate.
Then I will get the seconds part and convert them to an integer.
Now the order by could be reached ordering for the first part (TTI) and then for the second part (TTS) without using an UNION.
But there is a problem, some dates contains a month with only 1 char, (es: 4 april vs 12 december) luckily, we can use the IIF operator to select the right numbers for mid, right and left.

EDIT:

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;

Answer 2

我的本能是创造功能,在“获取最新资料”的声明中管理功能,以便转换所储存的价值,使之与所期望的格式相匹配。

The quick & dirty function below copes with both single and two digit months. It requires Access 2000 or later.

? 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 OrderStatus
SET time_field = FixTimeData(time_field)
WHERE time_field Like "*/*/*/*";

如果你根据《家庭福利法》而不是《家庭福利法》,改用《家庭福利法》条款中的《家庭福利法》野生心脏。

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

或者,如果你想要在阿农发组织或农林部下做同样的工作的话,则使用阿农联野生生物卡。

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

我将<条码>时间_field作为使用<条码>的外地名称,因为<条码> 日<>/代码>是一个保留词。如果你可以更改外地名称,在你的询问中附上方括号。

Edit:我打算确定数据,以便你能够根据<条码>可靠地进行分类(时间_field,11)。

www.un.org/spanish/ecosoc Edit2:根据“upper”分钟数进行分类,看看你是否能够以这种方法为基础,假设你确定了所储存的日期/时间范围:

? 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(> 功能接受方言,但我可能使用CDate(<>>>/code>,在将其添加到Minute(>之前,将说明明确改为日期/时间。



因此,......如果我无所作为,......在这样的问询中尝试这种做法:

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

Answer 3

提出日期是实际的,而不是说明。将所有价值分到新的领域,更新2份,其中选用旧格式的所有行,另选新版。日间插座栏,得到适当分类的数据。

If you really want to keep it as string at least make sure to use standard ISO8601 format (approximately YYYY-MM-DDTHH:MM.ssss) that is safe for sorting/localization.

友情链接