CODEWITHSUNDEEP
oracle-databasedatetime
Carlota
Carlota

Reputation: 1237

Oracle sql how to get the date of a week

I have the following query that gets the week of a date:

SELECT   pdm.serie, rta.matricula_ant, TO_CHAR (fecha, 'ww') semana,
         SUM (rta.kms_acumulados) kms,
           COUNT
              (DISTINCT (CASE
                    WHEN v.secuencia BETWEEN rta.sec_origen AND rta.sec_destino
                       THEN v.cod_inc
                    ELSE '0'
                 END
                )
              )
         - 1 numincidencias
    FROM (SELECT ms.tren, ms.fecha_origen_tren, ms.secuencia, ri.cod_inc
            FROM r_incidencias ri, mer_sitra ms
           WHERE ri.cod_serv = ms.tren
             AND ri.fecha_origen_tren = ms.fecha_origen_tren
             AND ri.cod_tipoin IN (SELECT cod_tipo_iincidencia
                                     FROM v_tipos_incidencias
                                    WHERE grupo = '45')
             AND ri.punto_desde = ms.cod_estacion) v,
         r_trenes_asignar rta,
         r_maquinas rm,
         planificador.pl_dh_material pdm
   WHERE rta.fecha BETWEEN TO_DATE ('21/09/2018', 'dd/mm/yyyy') AND TO_DATE ('21/09/2018',
                                                                  'dd/mm/yyyy'
                                                                 )
     AND rta.serie >= 4000
     AND rta.matricula_ant IS NOT NULL
     AND rm.matricula_maq = rta.matricula_ant
     AND rm.cod_serie = pdm.id_material
     AND rta.grafico BETWEEN pdm.desde AND pdm.hasta
     AND v.tren(+) = rta.tren
     AND v.fecha_origen_tren(+) = rta.fecha
GROUP BY pdm.serie, rta.matricula_ant, TO_CHAR (fecha,  'ww')
ORDER BY pdm.serie, rta.matricula_ant, TO_CHAR (fecha, 'ww')

For example week 1

I want to display

week 1 : 1 january - 7 january

How can I get this?

Upvotes: 3

Views: 2142

Answers (1)

O. Jones
O. Jones

Reputation: 108839

Oracle offers the TRUNC(datestamp, format) function to manipulate dates this way. You may use a variety of format strings to get the first day of a quarter, year, or even the top of the hour.

Given a particular datestamp value, Oracle returns midnight on the first day of the present week with this expression:

  TRUNC(datestamp,'DY')

You can add days to a datestamp. Therefore this expression gives you midnight on the last day of the week

  TRUNC(datestamp,'DY') + 6

A WHERE-clause selector for all rows in the present week might be this.

  WHERE datestamp >= TRUNC(SYSDATE,'DY')
    AND datestamp < TRUNC(SYSDATE,'DY') + 7

Notice that the end of the range is just before (<) midnight on the first day of the next week. You need that because you may have datestamps after midnight on the last day of the week. (Beware using BETWEEN for datestamp ranges.)

And,

SELECT TO_CHAR(TRUNC(SYSDATE,'DY'),'YYYY-MM-DD'),
       TO_CHAR(TRUNC(SYSDATE,'DY')+6,'YYYY-MM-DD')
  FROM DUAL;

displays the first and last dates of the present week in ISO-like format.

Date arithmetic is cool. It's worth your trouble to study the date-arithmetic functions in your DBMS at least once a year.

Upvotes: 2

Related Questions