时间戳

首页 > 解决方案 > Oracle sql如何获取两个日期之间的开始和结束日期

oracle - Oracle sql如何获取两个日期之间的开始和结束日期

问题描述

我想在两个日期之间获取每周的开始和结束日期。日期的格式是 dd/mm/yyy hh24:mi:ss。我需要格式为 dd/mm/yyyy hh24:mi:ss 的星期，因为我必须用时间计算一周的开始日和结束日之间的天数和小时数

我写了这个声明

WITH 
date_range AS (

SELECT
            pdm.des_comercial serie,
                pdm.id_material codserie,
                ri.id_accion intervencion,
                TO_CHAR(NVL(ri.fecha_salida_rev, SYSDATE), 'dd/mm/RRRR') fecha1,
                to_char((CASE
                    WHEN ri.fecha_salida_rev > TO_DATE('18/06/2019', 'dd/mm/yyyy') THEN TO_DATE('18/06/2019', 'dd/mm/yyyy')
                    WHEN ri.fecha_salida_Rev IS NULL THEN TO_DATE('18/06/2019', 'dd/mm/yyyy')
                    ELSE ri.fecha_salida_Rev
                END),'dd/mm/yyyy hh24:mi:ss') fechasalida,
                to_char((CASE
                    WHEN ri.fecha_entrada_rev < TO_DATE('01/06/2019', 'dd/mm/yyyy') THEN TO_DATE('01/06/2019', 'dd/mm/yyyy')
                    ELSE ri.fecha_entrada_Rev
                END),'dd/mm/yyyy hh24:mi:ss') fechaentrada
                ,
                ri.cod_taller_rev,
                ri.COD_MATRICULA,
                ri.fecha_entrada_rev start_date,
                ri.fecha_salida_rev end_date
            FROM
                r_intervencion ri,
                planificador.pl_dh_material pdm
            WHERE
                ri.id_accion = ri.amortizada_por
                AND ri.causa_entrada = 1
                AND ri.tipo_accion = 1
                AND pdm.id_material = ri.cod_serie
                AND pdm.hasta = 99999999
                AND ri.ID_ACCION = 'IM4'
                AND ri.fecha_salida_rev BETWEEN TO_DATE('01/06/2019', 'dd/mm/yyyy') AND TO_DATE('18/06/2019', 'dd/mm/yyyy')
                 ),

semanas AS (            


SELECT  LEVEL "Week"

     ,to_char(to_date(start_date,'dd/mm/yyyy hh24:mi:ss') + (7 * (LEVEL - 1)),'IW') startweek

       ,to_char(to_date(start_date ,'dd/mm/yyyy hh24:mi:ss')+ (7 * (LEVEL - 1)),'IW') + 6 endweek

       ,TO_CHAR(start_date + (7 * (LEVEL - 1)),'IW') "Iso Week",
       serie,
       codserie,
       intervencion,
       cod_taller_rev,
       cod_matricula,
       fechaentrada,
       fechasalida,
       start_date,
       end_date

FROM   date_range
CONNECT  BY LEVEL <= (to_char(To_date(end_date,'dd/mm/yyyy hh24:mi:ss'),'IW') - to_char(To_date(start_date,'dd/mm/yyyy hh24:mi:ss'),'IW')) / 7 + 1 

) 

SELECT startweek,
endweek,
to_date(endweek,'dd/mm/yyyy hh24:mi:ss') - to_date(startweek,'dd/mm/yyyy hh24:mi:ss')  dias,
serie,
codserie,
intervencion,
cod_taller_rev,
cod_matricula,
start_Date,
end_date,
fechaentrada,
fechasalida,
rd.descripcion
FROM semanas,r_depositos rd
WHERE cod_taller_rev = rd.cod_deposito

当我执行它时，我得到

查询执行失败

SQL Error [1840] [22008]: ORA-01840: ORA-01840: input value not long enough for date format

错误在

 ,to_char(to_date(start_date,'dd/mm/yyyy hh24:mi:ss') + (7 * (LEVEL - 1)),'IW') startweek

       ,to_char(to_date(start_date ,'dd/mm/yyyy hh24:mi:ss')+ (7 * (LEVEL - 1)),'IW') + 6 endweek

如何使用 dd/mm/yyyy hh24:mi:ss 格式获取 startweek 和 endweek

已编辑

start_date                   end_date

20/05/2019 20:00:00         05/06/2019  08:00:00

周

20/05/2019 20:00:00 26/05/2019  -> 6 days and xxx hours
27/05/2019          02/06/2019 -> 7 days
03/06/2019          05/06/2019 08:00:00 -> 3 days and xxx hours

我需要计算每周的天数和小时数的差异。

For example between 20/05/2019 20:00:00 and 26/05/2019
and last one between 03/06/2019 and 05/06/2019 08:00:00

我的问题是计算

to_date(endweek,'dd/mm/yyyy hh24:mi:ss') - to_date(startweek,'dd/mm/yyyy hh24:mi:ss')  dias,

endweek and startweek have to have dd/mm/yyyy hh24:mi:ss

标签： oracle

解决方案

你的endweek计算是

to_char(to_date(start_date ,'dd/mm/yyyy hh24:mi:ss')+ (7 * (LEVEL - 1)),'IW') + 6 endweek

这会尝试将数字 6 添加到字符串中。我怀疑你想要的是

to_char(to_date(start_date ,'dd/mm/yyyy hh24:mi:ss') + (7 * (LEVEL - 1) + 6),'IW') endweek

在这里，我已经移动了，+ 6因此您将 6 添加到日期值，而不是字符串。

推荐阅读