SQL 提取日期和时间 - (Teradata)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23506057/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXTRACT the date and time - (Teradata)
提问by Doug Fir
I am trying to extract the date and time from a field in Teradata.
我正在尝试从 Teradata 的字段中提取日期和时间。
The field in question is:
有问题的领域是：
VwNIMEventFct.EVENT_GMT_TIMESTAMP
Here is what the data look like:
下面是数据的样子：
01/02/2012 12:18:59.306000
I'd like the date and time only.
我只想要日期和时间。
I have tried using EXTRACT(Date, EXTRACT(DAY_HOURand a few others with no success.
我试过使用EXTRACT(Date，EXTRACT(DAY_HOUR还有其他一些没有成功。
DATE_FORMAT()does not appear to work since I'm on Teradata.
DATE_FORMAT()因为我在 Teradata 上，所以似乎不起作用。
How would I select the date and time from VwNIMEventFct.EVENT_GMT_TIMESTAMP?
我将如何从中选择日期和时间VwNIMEventFct.EVENT_GMT_TIMESTAMP？
回答by dnoeth
If the datatype of EVENT_GMT_TIMESTAMP is a TIMESTAMP, it's simple Standard SQL:
如果 EVENT_GMT_TIMESTAMP 的数据类型是 TIMESTAMP，则它是简单的标准 SQL：
CAST(EVENT_GMT_TIMESTAMP AS DATE)
CAST(EVENT_GMT_TIMESTAMP AS TIME)
If it's a CHAR you need to apply a FORMAT, too:
如果是 CHAR，则您也需要应用 FORMAT：
CAST(CAST(EVENT_GMT_TIMESTAMP AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:SS.s(6)') AS DATE)
CAST(CAST(EVENT_GMT_TIMESTAMP AS TIMESTAMP FORMAT 'dd/mm/yyyyBhh:mi:SS.s(6)') AS TIME)
Edit:
编辑：
For simply changing the display format you need to add a FORMAT and a CAST to a string:
为了简单地更改显示格式，您需要将 FORMAT 和 CAST 添加到字符串中：
CAST(CAST(EVENT_GMT_TIMESTAMP AS FORMAT 'YYYYMMDDHHMI') AS CHAR(12))
or
CAST(CAST(EVENT_GMT_TIMESTAMP AS FORMAT 'YYYYMMDDHHMISS') AS CHAR(14))
If you don't care about display, just want to truncate the seconds:
如果你不关心显示，只想截断秒数：
EVENT_GMT_TIMESTAMP - (EXTRACT(SECOND FROM EVENT_GMT_TIMESTAMP) * INTERVAL '1.000000' SECOND)
Working with timestamps is a bit tricky :-)
使用时间戳有点棘手:-)
回答by masospaghetti
I know this is an old topic, but I've struggled with this too. Try:
我知道这是一个古老的话题，但我也一直在努力解决这个问题。尝试：
CAST(EVENT_GMT_TIMESTAMP AS TIMESTAMP(0))
The result will be
结果将是
01/02/2012 12:18:59
The datatype will still be timestamp, but it will just be the date and time with no microseconds (looks just like a datetime object in Microsoft SQL).
数据类型仍然是时间戳，但它只是没有微秒的日期和时间（看起来就像 Microsoft SQL 中的日期时间对象）。
