提取日期和时间-(Teradata) [英] EXTRACT the date and time - (Teradata)

问题描述

我正在尝试从Teradata中的字段中提取日期和时间.

I am trying to extract the date and time from a field in Teradata.

有问题的字段是:

VwNIMEventFct.EVENT_GMT_TIMESTAMP

这是数据的样子:

01/02/2012 12:18:59.306000

我只想要日期和时间.

我尝试使用 EXTRACT(Date ， EXTRACT(DAY_HOUR )和其他一些方法都没有成功.

I have tried using EXTRACT(Date, EXTRACT(DAY_HOUR and a few others with no success.

DATE_FORMAT()似乎无法正常工作.

如何从 VwNIMEventFct.EVENT_GMT_TIMESTAMP 中选择日期和时间?

How would I select the date and time from VwNIMEventFct.EVENT_GMT_TIMESTAMP?

推荐答案

如果EVENT_GMT_TIMESTAMP的数据类型是TIMESTAMP，则它是简单的标准SQL:

If the datatype of EVENT_GMT_TIMESTAMP is a TIMESTAMP, it's simple Standard SQL:

CAST(EVENT_GMT_TIMESTAMP AS DATE)
CAST(EVENT_GMT_TIMESTAMP AS TIME)

如果是CHAR，则也需要应用FORMAT:

If it's a CHAR you need to apply a FORMAT, too:

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)

要简单地更改显示格式，您需要在字符串中添加FORMAT和CAST:

For simply changing the display format you need to add a FORMAT and a CAST to a string:

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 :-)

这篇关于提取日期和时间-(Teradata)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！