将军事时间转换为标准时间误差 [英] Convert Military Time to Standard Time Error

问题描述

我有一个数据集，该数据集以小时/分钟的格式拉回访问时间（例如-12:33 PM为1233）。我正在尝试使用 DATEPART 函数，但是它一直给我一个语法错误，指出：

I have a data set that pulls back visit time in hour/min format (e.g. - 12:33PM would be 1233). I am trying to use the DATEPART function but it keeps giving me a syntax error stating that :

期望在'（'和'HOUR'关键字之间。

"expecting something between '(' and the 'HOUR' keyword.

下面的部分是SQL的一部分这给了我错误：

The section below is the piece of the SQL that is giving me errors:

CASE WHEN DATEPART(HOUR , VSL.VISIT_TIME) > 12  THEN DATEPART (HOUR, VSL.VISIT_TIME) -12 ELSE DATEPART (HOUR, VSL.VISIT_TIME) END AS THE_TIME

我正试图

推荐答案

通过一系列强制转换，您可以执行以下操作：

Doing this through a series of casts, you could do something like:

SELECT CAST(CAST(CAST(CAST((1420 * 100 (FORMAT '99:99:99')) AS CHAR(8)) AS time ) AS TIME FORMAT 'HH:MIBT') AS CHAR(8))

可以使用一些数学运算来对它进行排序并在最后强制转换：

Or you could use some math to sort it out and cast at the end:

SELECT 
  1420 AS intTime,
  FLOOR(intTime/100) AS intHours,
  intTime - (intHours * 100) AS intMinutes,
  intHours * 60 + intMinutes AS totalIntMinutes,
  TIME '00:00:00' + (totalIntMinutes * INTERVAL '1' MINUTE) AS totalTime,
  CAST(CAST(totalTime AS TIME FORMAT 'HH:MIBT') AS CHAR(8)) AS char12HrTime

或者只是解析和数学事物：

Or just parse and math the thing:

SELECT 
  1420 AS intTime,
  Trim(CASE WHEN FLOOR(intTime/100) > 12 then FLOOR(intTime/100) - 12 ELSE FLOOR(intTime/100) END) || ':' || TRIM(intTime - (FLOOR(intTime/100) * 100))

可能还有其他几种方法

无论用哪种方式剪切，这都会有点难看，因为您将时间存储为时间类型。

Either way you cut it this is going to be a little ugly since you are storing time as something other than a time type.

这篇关于将军事时间转换为标准时间误差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！