oracle SQL 如何从日期中删除时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13135552/
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
oracle SQL how to remove time from date
提问by Slim
I have a column named StartDatecontaining a date in this format: 03-03-2012 15:22
我有一个名为的列，StartDate其中包含以下格式的日期：03-03-2012 15:22
What I need is to convert it to date. It should be looking like this: DD/MM/YYYY
我需要的是将其转换为日期。它应该是这样的：DD/MM/YYYY
What I have tried without success is:
我尝试过但没有成功的是：
select
p1.PA_VALUE as StartDate,
p2.PA_VALUE as EndDate
from WP_Work p
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.WP_ID AND p1.NAME = 'StartDate'
LEFT JOIN PARAMETER p2 on p2.WP_ID=p.WP_ID AND p2.NAME = 'Date_To'
WHERE p.TYPE = 'EventManagement2'
AND TO_DATE(p1.PA_VALUE, 'DD/MM/YYYY') >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
AND TO_DATE(p2.PA_VALUE, 'DD/MM/YYYY') <= TO_DATE('26/10/2012', 'DD/MM/YYYY')
Is there a way to do this?
有没有办法做到这一点？
EDIT1: the PA_VALUEcolumn is: VARCHAR2
EDIT1：该PA_VALUE列是：VARCHAR2
采纳答案by APC
When you convert your string to a date you need to match the date mask to the format in the string. This includes a time element, which you need to remove with truncation:
当您将字符串转换为日期时，您需要将日期掩码与字符串中的格式相匹配。这包括一个时间元素，您需要将其删除并截断：
select
p1.PA_VALUE as StartDate,
p2.PA_VALUE as EndDate
from WP_Work p
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.WP_ID AND p1.NAME = 'StartDate'
LEFT JOIN PARAMETER p2 on p2.WP_ID=p.WP_ID AND p2.NAME = 'Date_To'
WHERE p.TYPE = 'EventManagement2'
AND trunc(TO_DATE(p1.PA_VALUE, 'DD-MM-YYYY HH24:MI')) >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
AND trunc(TO_DATE(p2.PA_VALUE, 'DD-MM-YYYY HH24:MI')) <= TO_DATE('26/10/2012', 'DD/MM/YYYY')
回答by Habib
You can use TRUNCon DateTime to remove Time part of the DateTime. So your where clause can be:
您可以在 DateTime 上使用TRUNC来删除 DateTime 的时间部分。所以你的 where 子句可以是：
AND TRUNC(p1.PA_VALUE) >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
The TRUNCATE (datetime) function returns date with the time portion of the day truncated to the unit specified by the format model.
TRUNCATE (datetime) 函数返回日期，其中日期的时间部分被截断为格式模型指定的单位。
回答by Jar Yit
We can use TRUNC function in Oracle DB. Here is an example.
我们可以在 Oracle DB 中使用 TRUNC 函数。这是一个例子。
SELECT TRUNC(TO_DATE('01 Jan 2018 08:00:00','DD-MON-YYYY HH24:MI:SS')) FROM DUAL
Output: 1/1/2018
输出：1/1/2018
回答by pavelpopov
Try
尝试
SELECT to_char(p1.PA_VALUE,'DD/MM/YYYY') as StartDate,
to_char(p2.PA_VALUE,'DD/MM/YYYY') as EndDate
...
回答by it_iz_code
If your column with DATE datatype has value like below : -
如果您的 DATE 数据类型列具有如下值：-
value in column : 10-NOV-2005 06:31:00
列中的值：2005 年 11 月 10 日 06:31:00
Then, You can Use TRUNC function in select query to convert your date-time value to only date like - DD/MM/YYYY or DD-MON-YYYY
然后，您可以在选择查询中使用 TRUNC 函数将您的日期时间值转换为仅日期，例如 - DD/MM/YYYY 或 DD-MON-YYYY
select TRUNC(column_1) from table1;
从 table1 中选择 TRUNC(column_1)；
result : 10-NOV-2005
结果：2005 年 11 月 10 日
You will see above result - Provided that NLS_DATE_FORMAT is set as like below :-
您将看到以上结果 - 前提是 NLS_DATE_FORMAT 设置如下：-
Alter session NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
更改会话 NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
