尝试在Teradata中投射日期和时间 [英] Trying to cast date and time in Teradata

问题描述

我正在从一个具有timestamp（6）数据类型的表中进行选择。

我想将日期和时间精确到分钟，就像这样：

当前数据如下： 03/10/2014 21：54：47.690000

我想选择的结果如下： 03/10/2014 21:54:47

我尝试了此操作：

  SELECT CAST（日期为EVENT_GMT_TIMESTAMP）|| （EVENT_GMT_TIMESTAMP AS TIME）作为日期，SUM（VwNIMEventFct.DWELL_MINUTES）AS totalDwellMins RDMAVWSANDBOX.vwNIMEventFct 
 
 WHERE EXTRACT（MONTH FROM EVENT_GMT_TIMESTAMP）= 4 
 GROUP BYD code> 

但是收到一条消息语法错误，预计单词EVENT_GMT_TIMESTAMP和AS关键字之间会出现一些错误

也尝试了concat（）。

有没有比串联的更好的方法，而不是将EVENT_GMT_TIMESTAMP选择为日期时间，而是选择分钟？ p>

还有小时吗？

必须有一个函数可以让您选择这样的输出吗？

我发现研究网络的功能似乎无法在Teradata上运行。

我也尝试过以下操作：

 选择投射（EVENT_GMT_TIMESTAMP AS FORMAT'YYYYMMDDHHMM'）
  

但是输出是这样的：

  2014/4/18 12:20： 44.384000 
  

我需要像这样：

  04/18 / 2014 12:20:44 
  

解决方案

我从没去过能够使时间戳格式像我想要的那样正常工作，但这将为您提供所需的格式：

  SELECT current_timestamp（6 ）AS ts，
（ts（FORMAT'MM / DD / YYYY'）（CHAR（10）））as DT，
 cast（ts as time（6））（char（8））as tm，
 dt ||''|| tm as FormattedDate 
  

或者，以及改进的版本，对dnoeth表示赞赏：

  SELECT current_timestamp（6）AS ts，
 ts（FORMAT'MM / DD / YYYYbhh：mi：ss'）（CHAR（19））
  

I'm selecting from a table with a timestamp(6) data type.

I would like to get the date and time, to the minute, like so:

Currently the data look like this: 03/10/2014 21:54:47.690000

I would like to select so that the result looks like this: 03/10/2014 21:54:47

I tried this:

SELECT CAST(EVENT_GMT_TIMESTAMP AS DATE) || (EVENT_GMT_TIMESTAMP AS TIME)   AS theDate, SUM(VwNIMEventFct.DWELL_MINUTES) AS totalDwellMins
FROM RDMAVWSANDBOX.VwNIMEventFct
WHERE EXTRACT(MONTH FROM EVENT_GMT_TIMESTAMP) = 4
GROUP BY theDate

But received a message "Syntax error, expected something between the word EVENT_GMT_TIMESTAMP and the AS keyword"

Also tried concat().

Is there a better way, rather than concatenating, to select EVENT_GMT_TIMESTAMP as datetime but to the minute?

Also to the hour?

There must be a function that lets you select the output like this?

The ones I found researching the web do not appear to function on Teradata.

I also tried this:

SELECT CAST(EVENT_GMT_TIMESTAMP AS FORMAT 'YYYYMMDDHHMM')  

But the output was like this:

04/18/2014 12:20:44.384000

I need it to be like this:

04/18/2014 12:20:44

解决方案

I've never been able to get timestamp formatting to work quite like I want it, but this will get you the desired format:

SELECT  current_timestamp(6) AS ts,
  ( ts (FORMAT 'MM/DD/YYYY') (CHAR(10)))as DT, 
  cast (ts as time(6)) (char(8)) as tm,
   dt ||' ' ||tm as FormattedDate

Or, there's the new and improved version, compliments of dnoeth:

SELECT  current_timestamp(6) AS ts,
ts (FORMAT 'MM/DD/YYYYbhh:mi:ss') (CHAR(19))

这篇关于尝试在Teradata中投射日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！