在 Oracle SQL 中将时间戳转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37559741/
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
Convert timestamp to date in Oracle SQL
提问by Dinu
How can we convert timestamp to date?
我们如何将时间戳转换为日期？
The table has a field, start_tswhich is of the timestampformat:
该表有一个字段，start_ts其timestamp格式为：
'05/13/2016 4:58:11.123456 PM'
I need to query the table and find the maximum and min timestampin the table but I'm not able to.
我需要查询表并找到表中的最大值和最小值timestamp，但我不能。
Select max(start_ts)
from db
where cast(start_ts as date) = '13-may-2016'
But the query is not returning any values.
但是查询没有返回任何值。
Please help me in finding the max timestamp for a date.
请帮助我找到日期的最大时间戳。
回答by Peter Nosko
CAST(timestamp_expression AS DATE)
For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;
例如，查询是： SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;
回答by Felix Pamittan
Try using TRUNCand TO_DATEinstead
尝试使用TRUNCandTO_DATE代替
WHERE
TRUNC(start_ts) = TO_DATE('2016-05-13', 'YYYY-MM-DD')
Alternatively, you can use >=and <instead to avoid use of function in the start_tscolumn:
或者，您可以使用>=and<来避免在start_ts列中使用函数：
WHERE
start_ts >= TO_DATE('2016-05-13', 'YYYY-MM-DD')
AND start_ts < TO_DATE('2016-05-14', 'YYYY-MM-DD')
回答by theDbGuy
use this formatting while selecting
选择时使用此格式
to_char(systimestamp,'DD-MON-YYYY')
Eg:
例如：
select to_char(systimestamp,'DD-MON-YYYY') from dual;
从双中选择 to_char(systimestamp,'DD-MON-YYYY');
回答by Used_By_Already
If the datatype is timestamp then the visible format is irrelevant.
如果数据类型是时间戳，则可见格式无关紧要。
You should avoid converting the data to date or use of to_char. Instead compare the timestamp data to timestamp values using TO_TIMESTAMP()
您应该避免将数据转换为日期或使用 to_char。而是使用 TO_TIMESTAMP() 将时间戳数据与时间戳值进行比较
WHERE start_ts >= TO_TIMESTAMP('2016-05-13', 'YYYY-MM-DD')
AND start_ts < TO_TIMESTAMP('2016-05-14', 'YYYY-MM-DD')
回答by Youness Marhrani
You can use: select to_date(to_char(date_field,'dd/mm/yyyy')) from table.
您可以使用：从表中选择 to_date(to_char(date_field,'dd/mm/yyyy'))。
回答by Dinu
This may not be the correct way to do it. But I have solved the problem using substring function.
这可能不是正确的方法。但是我已经使用 substring 函数解决了这个问题。
Select max(start_ts), min(start_ts)from db where SUBSTR(start_ts, 0,9) ='13-may-2016'
using this I was able to retrieve the max and min timestamp.
使用这个我能够检索最大和最小时间戳。
