2021-06-03 Oracle DATE类型和字符串间的转换

常见DATE类型操作

select sysdate  from dual; 
-- 2021-06-03 15:27:15

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; 
-- 2021-06-03 15:27:15
select to_date('2021-06-03 23:59:59','yyyy-MM-dd hh24:mi:ss') from dual;
-- 2021-06-03 23:59:59
select to_date(concat(to_char(sysdate,'yyyy-MM-dd'),' 23:59:59'),'yyyy-MM-dd hh24:mi:ss') from dual;
-- 2021-06-03 23:59:59

select  to_char(sysdate, 'yyyy' )  from dual; 
-- 年 
-- 2021

select  to_char(sysdate, 'MM' )  from dual;
-- 月 
-- 06

select  to_char(sysdate, 'dd' )  from dual;
-- 日 
-- 03

select  to_char(sysdate, 'Q')  from dual;
-- 季 
-- 2

select  to_char(sysdate, 'iw')  from dual;
-- 周
-- 22
-- 按日历上的那种，每年有52或者53周

自建表测试

用于测试的表结构和数据

CREATE TABLE "VICEL"."TEST" 
   (    "BATCH" VARCHAR2(255), 
    "NAME" VARCHAR2(255), 
    "PRODUCE_DATE" DATE
   ) ;
     
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '产品1', TO_DATE('2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '产品1', TO_DATE('2021-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '产品3', TO_DATE('2021-05-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '产品3', TO_DATE('2021-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次1', '产品2', TO_DATE('2021-05-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '产品2', TO_DATE('2021-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '产品2', TO_DATE('2021-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '产品1', TO_DATE('2021-05-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '产品1', TO_DATE('2021-06-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次2', '产品3', TO_DATE('2021-06-03 01:30:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '产品3', TO_DATE('2021-06-03 23:30:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '产品3', TO_DATE('2021-06-03 23:59:59', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '产品2', TO_DATE('2021-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '产品2', TO_DATE('2021-05-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "VICEL"."TEST"("BATCH", "NAME", "PRODUCE_DATE") VALUES ('批次3', '产品1', TO_DATE('2021-05-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'));

示例查询语句

SELECT
    * 
FROM
    TEST 
WHERE
-- 日期->字符串->日期
-- 当前系统时间为2021-06-03
    TEST.PRODUCE_DATE >=to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd hh24:mi:ss')
    AND TEST.PRODUCE_DATE <=to_date(concat(to_char(sysdate,'yyyy-MM-dd'),' 23:59:59'),'yyyy-MM-dd hh24:mi:ss');

SELECT
    * 
FROM
    TEST 
WHERE
-- 字符串->日期
    TEST.PRODUCE_DATE >=to_date('2021-06-03','yyyy-MM-dd hh24:mi:ss')
    AND TEST.PRODUCE_DATE <=to_date(concat('2021-06-03',' 23:59:59'),'yyyy-MM-dd hh24:mi:ss');