Geek asks and answers

Sort access data (input time in the wrong format)

Problem:

Some fields in my table have the wrong date format, they are formatted using the .ToString () method in C #, but I made a typo and entered it like this: MM / dd / yyyy hh: mm / ss tt

Now, if you look carefully, you will see MM / dd / yyyy hh: mm ----> ' / ' <--- ss

which should be : '

I was preceded to fix it in my C # code (this means that some of the values ​​are formatted: MM / dd / yyyy hh: mm: ss tt )

The problem is that I'm trying to select values ​​from a table in Date order (which I cannot just sort by 'Datefieldnamehere'), because some of the fields are in the wrong format with the character '/'

What I tried:

Now, I realized that time is always in the same place (and that is important to me), I could just take a substring of numbers for the time and order them; first for AM , then for PM

I could order by MID ([ColumnName], 11,2), MID ([ColumnName], 14,2), MID ([ColumnName], 16,2), because each date, regardless of the separator, is always in M ​​format / dd / yyyy hh / mm / ss 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 find out if it will order it by the hour, but it does not give me this: Err

As you can see, it goes (in the hour field) 01, then 02, and then returns to 01 ...?

, . , , ,

Edit: , , ; , , , , //, , - :  Order By Val ( (ColumnName, StartPos, EndPos))

: .

+3
3

, .
Left, Mid Right CDate CInt.
(16 ), ": 00" PM/AM. CDate.
.
(TTI), (TTS) UNION.
, 1 char, (es: 4 12 ), , IIF , .

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;
+1

- 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, wild card ANSI WHERE.

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

ANSI wild cards ALike, , ADO DAO.

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

time_field , Time, Time . , .

. , , Right(time_field, 11)

Edit2: , "" , , , , /:

? 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() /, Minute().

... ... :

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

, . - , - . .

If you really want to save it as a string, at least be sure to use the standard ISO8601 format (approximately YYYY-MM-DDTHH: MM.ssss), which is safe for sorting / localization.

+2
source

More articles:


All Articles