Sql query date range in reverse

Question

Anyone know if I can make a reverse Between?

Something like this:

SELECT * 
FROM TABLE 
WHERE '01-01-2014' BETWEEN 'field1' AND 'field2';

Adrian Wragg · Accepted Answer · 2014-06-10 10:57:05Z

1

If by "reverse between" you mean "not between", you can do:

where '2014-01-01' not between field1 and field2

You should use YYYY-MM-DD for date constants (the ISO standard). Or explicitly convert from a string to a date using a specific conversion function.

edited Jun 10, 2014 at 10:57

Adrian Wragg

7,4193 gold badges30 silver badges52 bronze badges

answered Jun 10, 2014 at 10:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ngrashia · Accepted Answer · 2014-06-10 10:55:20Z

0

SELECT * FROM TABLE WHERE 
TO_DATE('01-01-2014', 'DD-MM-YYYY') 
        BETWEEN 
         TRUNC(field1) AND TRUNC(field2);

Above query assumes your field1 and field2 belong to the "DATE" datatype.

Incase, field1 and field2 are strings then, your select query should be

SELECT * FROM TABLE WHERE 
TO_DATE('01-01-2014', 'DD-MM-YYYY') 
        BETWEEN 
         TO_DATE(field1,'DD-MM-YYYY') AND TO_DATE(field2,'DD-MM-YYYY');

EDIT:

In oracle, && is not valid, use AND instead. For date comparision as string, you are using "YYYY-MM-DD" which would be valid, however, it is better to use TO_DATE Function since I assume data_expiracio would be a "DATE" datatype.

    SELECT grups.nom as grup, categoria.nom as categoria, zona.nombre as zona, ofertes.* FROM (((ofertes LEFT JOIN grups ON ofertes.id_grupo = grups.id) LEFT JOIN categoria ON ofertes.idcategoria = categoria.id) LEFT JOIN zona ON ofertes.idzona = zona.id) WHERE 
estat=1 
AND
data_expiracio >= TO_DATE('2014-06-10' , 'YYYY-MM-DD')

AND TO_DATE(2014-06-04,'YYYY-MM-DD') BETWEEN 
TRUNC(dataoferta) AND TRUNC(datavenciment) ORDER BY datavenciment ASC

edited Jun 10, 2014 at 10:55

answered Jun 10, 2014 at 10:35

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

3 Comments

Microseu Over a year ago

SELECT grups.nom as grup, categoria.nom as categoria, zona.nombre as zona, ofertes.* FROM (((ofertes LEFT JOIN grups ON ofertes.id_grupo = grups.id) LEFT JOIN categoria ON ofertes.idcategoria = categoria.id) LEFT JOIN zona ON ofertes.idzona = zona.id) WHERE estat=1 && data_expiracio >= '2014-06-10' && TO_DATE(2014-06-04,'YYYY-MM-DD') BETWEEN TRUNC(dataoferta) AND TRUNC(datavenciment) ORDER BY datavenciment ASC No returns nothing and I have records with that data, without adding the date piece of work.

ngrashia Over a year ago

Refer Edit in my Answer

Microseu Over a year ago

thanks for your help necesitava just put "not" before the "between"

Collectives™ on Stack Overflow

Sql query date range in reverse

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related