SQL Server 2008 -将十进制(4，0)转换为日期时间
SQL Server 2008 -将十进制(4，0)转换为日期时间
提问于 2015-01-28 20:30:40
我花了40分钟在这里和其他网站上寻找答案。问题背景:我的ERP系统记录了预订劳动力的日期和时间。它将预订日期记录为datetime，将预订时间记录为decimal(4)。我正在尝试转换时间(十进制到日期时间)，这样日期和时间就是一个值。
示例数据：
Job Op Date Time
----------------------------------
1 10 06/01/2015 254
1 20 06/01/2015 254
1 20 06/01/2015 542
1 20 06/01/2015 1347
1 20 07/01/2015 1340
1 30 07/01/2015 1408
1 30 07/01/2015 1340
1 30 08/01/2015 1037
1 40 06/01/2015 543
1 40 06/01/2015 1348
1 40 08/01/2015 1038
1 50 07/01/2015 1219
1 50 08/01/2015 1039
1 60 07/01/2015 1220
1 60 10/01/2015 1054
1 60 12/01/2015 859对于同一操作，一天内可以有多个预订。我的最终目标是找到作业编号上的操作的最早预订和最新预订。
Job Op Start End StartTime FinishTime
-------------------------------------------------------------
1 10 06/01/2015 06/01/2015 254 254
1 20 06/01/2015 07/01/2015 254 1340
1 30 07/01/2015 08/01/2015 1340 1037
1 40 06/01/2015 08/01/2015 543 1038
1 50 07/01/2015 08/01/2015 1219 1039
1 60 07/01/2015 12/01/2015 1220 859回答 2
Stack Overflow用户
发布于 2015-01-28 20:35:21
您可以使用row_number()和条件聚合来实现这一点：
select s.job, s.op,
max(case when seqnum = 1 then date end) as start,
max(case when seqnum = cnt then date end) as end,
max(case when seqnum = 1 then time end) as start_time,
max(case when seqnum = cnt then time end) as end_time
from (select s.*,
row_number() over (partition by job, op order by date, time) as seqnum,
count(*) over (partition by job, op) as cnt
from sample s
) s
group by job, op;注意:你问题的标题似乎与你想要实现的目标无关。
Stack Overflow用户
发布于 2015-01-28 21:08:08
从十进制(4，0)到时间的转换是：
DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), '00:00')它只是将您的总分钟数加到00:00中以获得时间，其中您的总分钟数计算为(hours * 60) + minutes，其中
Hours = FLOOR([Time] / 100)
Minutes = ([Time] % 100)然而，，如果还不算太晚，你应该完全放弃这种方法。
在SQL Server中，很少需要分别存储日期和时间列，最好的方法是使用单个DATETIME2列，如果出于任何原因需要单独的列(如索引)，请使用计算列，例如
ALTER TABLE T ADD StartDate AS CAST(StartDateTime AS DATE);如果必须分别存储这两列，那么至少应该使用TIME数据类型来存储时间，而不是使用DECIMAL(4, 0)。对于您的情况，最好的方法是解决实际问题，这是不正确的数据类型，而不是您提出的问题。如果您要这样做，您将拥有如下数据：
Job Op JobDateTime
----------------------------------
1 10 06/01/2015 02:54
1 20 06/01/2015 02:54
1 20 06/01/2015 05:42
1 20 06/01/2015 13:47
1 20 07/01/2015 13:40
1 30 07/01/2015 14:08
1 30 07/01/2015 13:40
1 30 08/01/2015 10:37
1 40 06/01/2015 05:43等。
那么您的查询就像这样简单
SELECT JOb, Op, FirstJob = MIN(JobDateTime), LastJob = MAX(JobDateTime)
FROM T
GROUP BY JOb, Op;执行此更正的代码为：
ALTER TABLE [YourTable] ADD JobDateTime DATETIME2 NOT NULL;
UPDATE [YourTable]
SET JobDateTime = DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), CAST([Date] AS DATETIME)));
ALTER TABLE [YourTable] DROP COLUMN [Date];
ALTER TABLE [YourTable] DROP COLUMN [Time];如果您已经有许多使用这些列的现有代码，则可以使用计算列为您执行计算：
ALTER TABLE YourTable
ADD JobDateTime AS DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), CAST([Date] AS DATETIME));例如：
CREATE TABLE #T (Job INT, Op INT, [Date] DATE, [Time] DECIMAL(4, 0));
INSERT #T (Job, Op, [Date], [Time])
VALUES
(1, 10, '2015-06-01', 254),
(1, 20, '2015-06-01', 254),
(1, 20, '2015-06-01', 542),
(1, 20, '2015-06-01', 1347),
(1, 20, '2015-07-01', 1340),
(1, 30, '2015-07-01', 1408),
(1, 30, '2015-07-01', 1340),
(1, 30, '2015-08-01', 1037),
(1, 40, '2015-06-01', 543),
(1, 40, '2015-06-01', 1348),
(1, 40, '2015-08-01', 1038),
(1, 50, '2015-07-01', 1219),
(1, 50, '2015-08-01', 1039),
(1, 60, '2015-07-01', 1220),
(1, 60, '2015-10-01', 1054),
(1, 60, '2015-12-01', 859);
ALTER TABLE #T
ADD JobDateTime AS DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), CAST([Date] AS DATETIME));
SELECT Job,
Op,
FirstJob = MIN(JobDateTime),
LastJob = MAX(JobDateTime)
FROM #T
GROUP BY Job, Op;如上所示，我建议只将日期和时间作为单个列返回，但如果您确实希望将它们作为单独的列返回，则至少应该使用TIME类型：
无论哪种方式，我都建议返回一个完整的日期时间，或者至少使用日期和时间列，例如
SELECT Job,
Op,
[Start] = CAST(MIN(JobDateTime) AS DATE),
[End] = CAST(MAX(JobDateTime) AS DATE),
StartTime = CAST(MIN(JobDateTime) AS TIME),
EndTime = CAST(MAX(JobDateTime) AS TIME)
FROM #T
GROUP BY Job, Op;SELECT Job,
Op,
FirstJob = MIN(DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), CAST([Date] AS DATETIME))),
LastJob = MAX(DATEADD(MINUTE, (FLOOR([Time] / 100) * 60) + ([Time] % 100), CAST([Date] AS DATETIME)))
FROM #T
GROUP BY Job, Op;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28192327
复制相关文章
相似问题
腾讯云开发者
