TeradataSQL:字符串时间，添加到日期并与其他时间和数据进行比较 [英] TeradataSQL: Time to String, Add to Date and Compare to Another Time and Data

问题描述

我正在尝试找出最干净的方法来在Teradata SQL Assistant中进行比较.我有计划的开始日期(TimeStamp)，计划的开始时间(varchar)，实际的开始和结束时间(TimeStamp).我需要合并计划的开始日期和时间，并能够将其与实际的开始日期和结束日期和时间进行比较，而无需修改原始数据(因为这不是我的).我意识到预定开始时间[SST]是24小时制，带有AM/PM后缀，但是就像我之前说的那样，我无法更改它.

I'm trying to figure out the cleanest way to do a comparison in Teradata SQL Assistant. I have the scheduled start date (TimeStamp), the Schedule start time (varchar), actual start and end times (TimeStamp). I need to consolidate the scheduled start date and time and be able to compare it to the actual start and end date and time without modifying the original data (because it's not mine). I realize that the Scheduled Start Time [SST] is in a 24 hour time format with a AM/PM suffix, but like I said before, I can't change that.

我试图选择cast(substr(scheduled_start_date,1,5) as TIMESTAMP(0)) from DB.TBL，但收到无效时间戳"错误.以下是表格数据示例.

I tried to do select cast(substr(scheduled_start_date,1,5) as TIMESTAMP(0)) from DB.TBL but am getting the "Invalid timestamp" error. There is example table data below.

Sch Start Date        Sch Start Time    Actual Start            Actual End
09/11/2017 00:00:00   11:30 AM          09/11/2017  11:34:16    09/11/2017 11:58:00
05/26/2017 00:00:00   15:30 PM          05/26/2017  15:40:00    05/26/2017 15:55:15
11/06/2017 00:00:00   19:30 PM          11/06/2017  21:25:00    11/06/2017 21:45:00

谢谢！

推荐答案

您需要将计划开始时间转换为时间间隔，然后可以轻松地将其添加到开始日期:

You need to cast the schedule start time as an Interval, then you can easily add it to the start date:

scheduled_start_date
+ Cast(Substr(scheduled_start_time, 1,5) AS INTERVAL HOUR TO MINUTE)

以时间戳作为开始的 date 似乎表明这是从Oracle/SQL Server移植的?

A start date which is a timestamp seems to indicate this was ported from Oracle/SQL Server?

后缀为AM/PM的 24小时制也很奇怪.

这篇关于TeradataSQL:字符串时间，添加到日期并与其他时间和数据进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！