如何在日期字段中剥离时间戳(不是从日期字段剥离而是从保存日期/时间戳数据的varchar中剥离) [英] How to strip out the timestamps in the date field (not from a date field but rather a varchar holding date/timestamp data)
问题描述
所以我有如下数据:
DATE
2019 04 19 03:00:00
我希望它看起来像这样:
I want it to look like this:
DATE
2019 04 19
或
DATE
04-19-2019
唯一的陷阱是保存数据的字段是varchar,所以我在下面尝试的内容不起作用.
The only catch is the field holding the data is a varchar so what I tried below doesn't work.
select to_char(DATE, 'YYYY/MM/DD') as dateonly, from table_name;
我也尝试使用以下内容:
I've also tried using the following:
select trunc(DATE) as date_only from table_name;
但是我得到的值看起来像这样:
But I get values that look like this:
2019 04 19 00:00:00
我想上面的方法可以工作,但是有没有办法摆脱上面我使用的trunc方法导致的结尾00:00:00?
I guess the above could work but is there a way I can get rid of the trailing 00:00:00s that result from the trunc method that I used above?
推荐答案
如果您的数据格式始终像 yyyy mm dd hh24:mi:ss ,那么您可以使用以下查询获取子字符串:
If your data format is always like yyyy mm dd hh24:mi:ss then you can use following query to get the sub string:
SELECT SUBSTR('2019 04 19 03:00:00', 1, 10)
参考: https://docs.oracle.com/database /121/SQLRF/functions196.htm#S
这篇关于如何在日期字段中剥离时间戳(不是从日期字段剥离而是从保存日期/时间戳数据的varchar中剥离)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!