oracle SQL如何从日期中删除时间 [英] oracle SQL how to remove time from date
本文介绍了oracle SQL如何从日期中删除时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为StartDate
的列,其中包含以下格式的日期:03-03-2012 15:22
I have a column named StartDate
containing a date in this format: 03-03-2012 15:22
我需要将其转换为日期.它应该看起来像这样:DD/MM/YYYY
What I need is to convert it to date. It should be looking like this: 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?
PA_VALUE
列为:VARCHAR2
推荐答案
将字符串转换为日期时,需要将日期掩码与字符串中的格式匹配.其中包括一个时间元素,您需要通过截断将其删除:
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')
这篇关于oracle SQL如何从日期中删除时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文