SQL 如何使用 Netezza 将日期时间转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21392490/
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
How to convert Date-time into Date using Netezza
提问by moe
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;
回答by Niederee
回答by thagraybush
Netezza has built-in function for this by simply using:
Netezza 具有内置功能，只需使用：
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_CHARmethod will not)
这将只返回timetamp的日期部分和有用得多比它强制转换为一个字符串TO_CHAR()，因为它会在工作GROUP BY，HAVING并与其他Netezza公司日期函数。（TO_CHAR方法不会）
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_TRUNC()函数将从时间戳（'日'、'月'、'年' 等）中提取特定值，但不会超过其中一个没有多个函数并连接。
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_DATEa lot, which is Oracle's function for this but will not work on Netezza.
DATE() 是对此的完美而简单的答案，我很惊讶在 Stack 上看到这个问题有这么多误导性的答案。我看到TO_DATE了很多，这是 Oracle 的功能，但不适用于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;
回答by Gordon Linoff
Use date_trunc()with the first argument of 'day'. I think this is what you want:
使用date_trunc()与第一个参数'day'。我认为这就是你想要的：
SELECT EF.DSCH_TS,
(case when date_trunc('day', EF.DSCH_TS) >= date_trunc('day', EF.ADMT_TS) THEN 'GroupA' END) AS CAL
FROM MainTable EF;
