如何将现有数据中的 24 小时转换为 12 小时

2021-09-10 00:00:00 sql tsql sql-server

我想知道我的 sql 如何像这样我的问题是每次我删除代码中的 where 子句时都会出现错误(从字符串转换日期和/或时间时转换失败.)我删除了 where 子句，因为我想看我所有的数据，下图只是我有这么多数据的例子

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

现场演示

现在，让我们谈谈您遇到的实际问题.

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

您将日期存储为字符串，这是不好的，始终为您的数据选择正确的数据类型，因此您需要将日期存储为 DATE.同样对于 Entries 那里有 3 个信息，这意味着缺乏规范化，因为它应该是 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);

相关文章