oracle - 从时间中提取小时并添加转换为小时的分钟并从时间中提取分钟并找到提醒（模数）

问题描述

我正在尝试从时间中提取小时并将分钟转换为小时，并从时间中提取分钟并在 Oracle SQL 中找到提醒（模）。到目前为止，我检查了几个链接并尝试用谷歌搜索，但找不到任何解决方案。到目前为止，这是我尝试做的

                     SELECT
                    CONCAT(
                        -- extract hours from time and add minutes converted to hours
                        (SUM(SUBSTR(m.Time, ':', 1)) + TRUNCATE((SUM(SUBSTR(m.Time, ':', -1)) / 60),0)),
                        ':', 
                        -- extract minutes from time and find reminder (modulo)
                        LPAD((SUM(SUBSTR(m.Time, ':', -1)) MOD 60), 2, 0)) hours,
                    CONCAT(CONCAT(u.Surname, ' '),u.Lastname) AS Username,                           
                    u.UserID AS UserId,
                    ut.Date_,
                    m.MomentID
                FROM
                    moments m
                LEFT JOIN
                    user_timesheets ut
                ON
                    ut.UserTimesheetsID = m.UserTimesheetsID
                LEFT JOIN
                    users u
                ON
                    ut.user_id = u.UserID
                WHERE
                EXTRACT (MONTH FROM TO_DATE(ut.Date_,'YYYY-MM-DD')) = '2020-01-21'
                  -- EXTRACT (MONTH FROM TO_DATE(ut.Date_)) = '2020-07-30'
                AND
                 EXTRACT (YEAR FROM TO_DATE(ut.Date_,'YYYY-MM-DD')) = '2020-01-21'
                  -- EXTRACT (YEAR FROM TO_DATE(ut.Date_)) = '2020-07-30'
                AND
                    ut.user_id = 1
                AND
                    (SELECT MAX(MomentID) FROM moments WHERE m.parent = m.MomentID) IS NULL
                AND
                    m.AtestStatus = 1

这里的问题是当我运行这个查询时出现错误

The identifier name started with an ASCII character other than a
           letter or a number. After the first character of the identifier
           name, ASCII characters are allowed including "$", "#" and "_".
           Identifiers enclosed in double quotation marks may contain any
           character other than a double quotation. Alternate quotation
           marks (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters. For all other contexts, consult the SQL Language
           Reference Manual.
*Action:   Check the Oracle identifier naming convention. If you are
           attempting to provide a password in the IDENTIFIED BY clause of
           a CREATE USER or ALTER USER statement, then it is recommended to
           always enclose the password in double quotation marks because
           characters other than the double quotation are then allowed.

如果这个问题对你来说很愚蠢，我很抱歉，但我完全是 PL/SQL 的初学者，我只是在寻求帮助。干杯:)

更新

由于我将 % 更改为MOD现在，我得到了错误

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Error at Line: 7 Column: 64

标签： oracleplsql

oracle - 从时间中提取小时并添加转换为小时的分钟并从时间中提取分钟并找到提醒（模数）

问题描述

解决方案

推荐阅读