如何使用Netezza将日期时间转换为Date [英] How to convert Date-time into Date using Netezza

问题描述

我正在做一些计算，但我的计算是关闭的，因为我的日期字段显示时间戳，我只想在我进行计算时仅使用日期。我如何忽略分钟，只是使用计算日期？这是我有的：

  SELECT EF.DSCH_TS，
 CASE WHEN EXTRACT（DAY from EF.DSCH_TS  -  EF .ADMT_TS）> = 0 THEN'GroupA'END AS CAL 
 FROM MainTable EF; 
  

解决方案

Netezza内置功能，只需使用：

  SELECT DATE（STATUS_DATE）AS DATE，
 COUNT（*）AS NUMBER_OF_ 
 FROM X 
 GROUP BY DATE（STATUS_DATE）
 ORDER BY DATE（STATUS_DATE）ASC 
  

将只返回时间表的日期部分，比将其转换为 TO_CHAR（）的字符串更有用，因为它可以在 GROUP BY ， HAVING 以及其他netezza日期功能。 （其中 TO_CHAR 方法将不会）

另外， DATE_TRUNC（） / code>函数将从Timestamp（'Day'，'Month，'Year'等等）中拉出一个特定值，但不超过其中一个没有多个函数并连接。

DATE（）是对此完美而简单的回答，令我惊讶的是在Stack上看到这么多误导性的答案。我看到 TO_DATE ，这是Oracle的功能，但不能在 Netezza 上运行。

假设您对两个时间戳的午夜到午夜之间的时间感兴趣，您的查询将如下所示：

  SELECT EF.DSCH_TS，
 CASE 
 WHEN EXTRACT（DAY FROM（DATE（EF.DSCH_TS） -  DATE（EF.ADMT_TS）））> = 0 THEN'GroupA'
 END AS CAL 
 FROM MainTable EF; 
  

I am doing some calculation but my calculation is off because my date field is showing the time-stamp and i only want to use as Date only when i am doing the calculation. How can i just ignore the minutes and just use the date when doing the calculation? Here is what i have:

SELECT EF.DSCH_TS,
       CASE WHEN EXTRACT (DAY FROM  EF.DSCH_TS - EF.ADMT_TS)>=0 THEN 'GroupA' END AS CAL
FROM MainTable EF;

解决方案

Netezza has built-in function for this by simply using:

SELECT DATE(STATUS_DATE) AS DATE,
       COUNT(*) AS NUMBER_OF_             
FROM X
GROUP BY DATE(STATUS_DATE)
ORDER BY DATE(STATUS_DATE) ASC

This will return just the date portion of the timetamp and much more useful than casting it to a string with TO_CHAR() because it will work in GROUP BY, HAVING, and with other netezza date functions. (Where as the TO_CHAR method will not)

Also, the DATE_TRUNC() function will pull a specific value out of Timestamp ('Day', 'Month, 'Year', etc..) but not more than one of these without multiple functions and concatenate.

DATE() is the perfect and simple answer to this and I am surprised to see so many misleading answers to this question on Stack. I see TO_DATE a lot, which is Oracle's function for this but will not work on Netezza.

With your query, assuming that you're interested in the days between midnight to midnight of the two timestamps, it would look something like this:

SELECT EF.DSCH_TS,
  CASE
    WHEN EXTRACT (DAY FROM (DATE(EF.DSCH_TS) - DATE(EF.ADMT_TS)))>=0 THEN 'GroupA'
  END AS CAL
FROM MainTable EF;

这篇关于如何使用Netezza将日期时间转换为Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！