Oracle - 如何检查我的日期字段值是否具有时间部分 [英] Oracle - How to check my date field value has time portion or not

问题描述

我有一个包含日期类型字段的表，它拥有几个具有日期值的记录与时间部分，少数记录只有日期值没有时间部分。如何检查哪个记录有时间部分和什么不是没有导航记录记录？

这是我正在使用的，但我想要一些更快的东西： / p>

  SELECT MY_TAB.SEQ，MY_TAB.CRTE_DT，
 CASE WHEN TRUNC（MY_TAB.CRTE_DT）= MY_TAB.CRTE_DT 
 THEN'False'ELSE'True'END AS有时间部分
 FROM（SELECT 1 AS SEQ，
 TO_DATE（'7/28/2013'，'MM / DD / YYYY'） AS CRTE_DT 
 FROM DUAL 
 UNION 
 SELECT 2 AS SEQ，
 TO_DATE（'11 / 07/2013 12:27:54'，'MM / DD / YYYY HH24：MI ：SS'）AS CRTE_DT 
 FROM DUAL）MY_TAB; 
  

解决方案

CREATE TABLE MY_TAB
AS
SELECT SEQ，CRTE_DT
FROM（SELECT 1 AS SEQ，TO_DATE（'7/28/2013'，'MM / DD / YYYY'）AS CRTE_DT FROM DUAL
UNION
SELECT 2 AS SEQ，
TO_DATE（'11 / 07/2013 12:27:54'，'MM / DD / YYYY HH24：MI：SS'）AS CRTE_DT FROM DUAL
UNION
SELECT 3 AS SEQ，
TO_DATE（'09 / 5/2013 00:00:00'，'MM / DD / YYYY HH24：MI：SS'）AS CRTE_DT FROM DUAL
UNION
SELECT 4 AS SEQ，TO_DATE（'4/15/2013 00:00:01'，'MM / DD / YYYY HH24：MI：SS'）AS CRTE_DT FROM DUAL
UNION
SELECT 5 AS SEQ，TO_DATE '12 / 12/2012 1:01:01'，'MM / DD / YYYY HH24：MI：SS'）AS CRTE_DT FROM DUAL）;

选择MY_TAB。 SEQ，MY_TAB.CRTE_DT，
CASE WHEN TRUNC（MY_TAB.CRTE_DT）= MY_TAB.CRTE_DT
THEN'False'ELSE'True'END ASH作为时间部分
FROM MY_TAB;

I have a table containing a date type field and it holds few records with date value with time portion and few records only have date value without time portion. How to check which record has time portion and what not without navigating record by record?

This is what I'm using at the moment, but I want something faster:

SELECT MY_TAB.SEQ, MY_TAB.CRTE_DT,
       CASE WHEN TRUNC (MY_TAB.CRTE_DT) = MY_TAB.CRTE_DT
       THEN 'False' ELSE 'True' END AS "Has Time Portion"
FROM (SELECT 1 AS SEQ,
             TO_DATE ('7/28/2013', 'MM/DD/YYYY') AS CRTE_DT
      FROM DUAL
      UNION
      SELECT 2 AS SEQ,
             TO_DATE ('11/07/2013 12:27:54', 'MM/DD/YYYY HH24:MI:SS') AS CRTE_DT
      FROM DUAL) MY_TAB;

解决方案

CREATE TABLE MY_TAB AS SELECT SEQ, CRTE_DT FROM (SELECT 1 AS SEQ, TO_DATE ('7/28/2013', 'MM/DD/YYYY') AS CRTE_DT FROM DUAL UNION SELECT 2 AS SEQ, TO_DATE ('11/07/2013 12:27:54', 'MM/DD/YYYY HH24:MI:SS') AS CRTE_DT FROM DUAL UNION SELECT 3 AS SEQ, TO_DATE ('09/5/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AS CRTE_DT FROM DUAL UNION SELECT 4 AS SEQ, TO_DATE ('4/15/2013 00:00:01', 'MM/DD/YYYY HH24:MI:SS') AS CRTE_DT FROM DUAL UNION SELECT 5 AS SEQ, TO_DATE ('12/12/2012 1:01:01', 'MM/DD/YYYY HH24:MI:SS') AS CRTE_DT FROM DUAL);

SELECT MY_TAB.SEQ, MY_TAB.CRTE_DT, CASE WHEN TRUNC (MY_TAB.CRTE_DT) = MY_TAB.CRTE_DT THEN 'False' ELSE 'True' END AS "Has Time Portion" FROM MY_TAB;

这篇关于Oracle - 如何检查我的日期字段值是否具有时间部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！