問題描述

我想知道我的 sql 如何像這樣我的問題是每次我刪除代碼中的 where 子句時都會出現(xiàn)錯誤(從字符串轉(zhuǎn)換日期和/或時間時轉(zhuǎn)換失敗.)我刪除了 where 子句，因為我想看我所有的數(shù)據(jù)，下圖只是我有這么多數(shù)據(jù)的例子

I want to know how my sql like this my problem is every i remove my where clause in my code there have a error (Conversion failed when converting date and/or time from character string.) i remove may where clause because i want to see my all data, the figure below is example only i have so many data

這是第一張桌子

| Entries               | recordDate              | Empid  | Reference |             
+-----------------------+-------------------------+--------+-----------+  
| 0016930507201907:35I  | 2019-05-07 00:00:00 000 | 001693 |   1693    |  
| 0016930507201917:06O  | 2019-05-07 00:00:00 000 | 001693 |   1693    |  
| 0016930507201907:35I  | 2019-05-08 00:00:00 000 | 001693 |   1693    |  
|                       | 2019-05-08 00:00:00 000 | 001693 |   1693    |

第二桌

| LastName | FirstName | middleName | EmployeeNO |
+----------+-----------+------------+------------+
| Cruz     | MA Kimberly  | Castillo   |   001693   |

這是我想看的

| Name                    | EmployeeNO | RecordDate              | TimeIn | TimeOut | 
+-------------------------+------------+-------------------------+--------+---------+
| CRUZ, MA  KIMBERLY, CASTILLO  |    001693  | 2019-05-07 00:00:00 000 |  07:35am |  05:06pm  |
| CRUZ, MA  KIMBERLY,CASTILLO   |    001693  | 2019-05-08 00:00:00 000 |  07:35am |

這是我的代碼，請幫助我，謝謝您的幫助

this is my code please help me thank you advance for your helping

Select 
    B.LastName + ',' + B.FirstName + ',' + B.MiddleName[Name] ,
    A.[RecordDate],
    B.[EmployeeNO],
    CONVERT(VARCHAR(08),MIN(IIF(ISNULL(CHARINDEX('I', A.[Entries], 0), 1) > 0, CAST( SUBSTRING(A.[Entries], LEN(A.[Entries]) - 5, 5) AS [TIME]), NULL)), 100) AS [TimeIn], 
    CONVERT(VARCHAR(08),MAX(IIF(ISNULL(CHARINDEX('O', A.[Entries], 0), 1) > 0,CAST(SUBSTRING(A.[Entries], LEN(A.[Entries]) - 5, 5) AS [TIME]), NULL)),100) AS [TimeOut]
FROM Employees [B]
INNER JOIN [DTR Upload] [A] ON B.EmployeeNo = A.EmpID
GROUP BY B.LastName, B.FirstName, B.MiddleName,B.[EmployeeNO], A.[recordDate]
ORDER BY A.[recordDate] asc, B.LastName +','+B.FirstName + ','+ B.MiddleName ASC

推薦答案

這就是你想要的嗎?

;WITH CTE AS
(
  SELECT EmployeeNO,
         CONCAT(LastName, ',', FirstName, ',', MiddleName) Name,
         RecordDate,
         CASE WHEN RIGHT(Entries, 1) = 'I' 
              THEN CAST(REPLACE(RIGHT(Entries, 6), 'I', '') AS TIME)
         END TimeIn,
         CASE WHEN RIGHT(Entries, 1) = 'O' 
              THEN CAST(REPLACE(RIGHT(Entries, 6), 'O', '') AS TIME)
         END TimeOut     
  FROM T1 INNER JOIN T2
  ON T1.EmpId = T2.EmployeeNO
)
SELECT EmployeeNO,
       Name,
       RecordDate,
       MIN(TimeIn) TimeIn,
       MAX(TimeOut) TimeOut
FROM CTE
GROUP BY EmployeeNO,
         Name,
         RecordDate;

退貨:

+------------+------------------------+-------------------------+----------+----------+
| EmployeeNO |          Name          |       RecordDate        |  TimeIn  | TimeOut  |
+------------+------------------------+-------------------------+----------+----------+
|       1693 | Cruz,Kimberly,Castillo | 2019-05-07 00:00:00 000 | 07:35:00 | 17:06:00 |
|       1693 | Cruz,Kimberly,Castillo | 2019-05-08 00:00:00 000 | 07:35:00 |          |
+------------+------------------------+-------------------------+----------+----------+

現(xiàn)場演示

現(xiàn)在，讓我們談?wù)勀龅降膶嶋H問題.

Now, let's talk a bit about the real problems you have.

您將日期存儲為字符串，這是不好的，始終為您的數(shù)據(jù)選擇正確的數(shù)據(jù)類型，因此您需要將日期存儲為 DATE.同樣對于 Entries 那里有 3 個信息，這意味著缺乏規(guī)范化，因為它應(yīng)該是 3 列.例如

You are storing dates as string which is bad, always pick the right data type for your data, so you need to store dates as DATE. Also for the Entries has 3 info there, that means a lack of normalization, because it should be 3 column instead. For example

+----------------+------+---------------------+
|    Entries     | Kind |      EntriesDate    |
+----------------+------+---------------------+
| 00169305072019 |    1 | 2019-05-07 07:35:00 |
| 00169305072019 |    0 | 2019-05-07 16:30:00 |
+----------------+------+---------------------+

這樣，您就不會陷入這些問題，事情就變得容易了.

This way, you won't fall in those issues and things becomes easy.

對于名字的拼接，如果你總是需要得到一個全名，我建議你使用計算列來做，那么你就不需要每次都拼接名字了

For the concatenation of the names, if you always needs to get a full name, I suggest that you use a computed column for that, then you don't need to concatenate the names every time

ALTER TABLE <Your Table Name Here>
ADD [FullName] AS CONCAT(LastName, ',', FirstName, ',', MiddleName);

這篇關(guān)于如何將現(xiàn)有數(shù)據(jù)中的 24 小時轉(zhuǎn)換為 12 小時的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！