Check date between two other dates spring data jpa

Question

I have this model:

public class Event {
    private String name;
    private Date start;
    private Date end;
}

and repository as

@Repository
public interface EventRepository extends JpaRepository<Event, Long> {
    List<Event> findByEventTypeAccount(Account account);
}

What I want to do is, I will pass one date and need to check that date is between start and end e.g. (I will pass Sept 30 as date and need to find all entries which have Sept 30 between their start and end)

Something like findDateisBetweenStartAndEnd(Date date)?

You should take a look the reference documentation: http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation. It's well explained. — Pau, Sep 30 '16 at 07:29

score 147 · Accepted Answer · edited Jun 20 '20 at 09:12

147

You should take a look the reference documentation. It's well explained.

In your case, I think you cannot use between because you need to pass two parameters

Between - findByStartDateBetween … where x.startDate between ?1 and ?2

In your case take a look to use a combination of LessThan or LessThanEqual with GreaterThan or GreaterThanEqual

LessThan/LessThanEqual

LessThan - findByEndLessThan … where x.start< ?1

LessThanEqual findByEndLessThanEqual … where x.start <= ?1

GreaterThan/GreaterThanEqual

GreaterThan - findByStartGreaterThan … where x.end> ?1

GreaterThanEqual - findByStartGreaterThanEqual … where x.end>= ?1

You can use the operator And and Or to combine both.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 30 '16 at 07:36

Pau

14,917
14
67
94

Hi will this be possible with String Values? So for example with a table of persons and select all between lastname = "abcd" to lastname= "efgh" – Ivor Sep 12 '18 at 14:26
@Pau Shouldn't the method names be `findByStartLessThan\Equal` and `findByStartGreaterThan\Equal` so it corresponds with the JPQL (i.e. `where x.start <\= ?1`and `where x.end >\= ?1`)? – domids Feb 12 '19 at 13:02
can you please tell me which format of date you are passing from client to call controller of method findDateisBetweenStartAndEnd?? I stuck with format part when i submit the request through client for the endpoint. – Indrajeet Gour Sep 19 '19 at 19:51
how to add where condition on another attribute ? between and where together in single query in spring data jpa? – Sumanth Varada Nov 21 '19 at 16:57

Michaël Cuypers · Answer 2 · 2018-03-08T10:34:34.573

66

I did use following solution to this:

findAllByStartDateLessThanEqualAndEndDateGreaterThanEqual(OffsetDateTime endDate, OffsetDateTime startDate);

edited Mar 08 '18 at 10:34

answered Mar 08 '18 at 10:13

Michaël Cuypers

722
5
5

3

You you please add a little (more detailed) explanation? – Yannick Huber Mar 08 '18 at 10:38
2

but for that you should pass two dates the start and the end one, I think is the right approach – Ivor Jan 24 '19 at 11:49
3

The order of the prams is wrong. First, param is related to startDate, not endDate – Jordan Silva May 20 '20 at 11:38
1

@JordanSilva , the order of params is correct as it tests is onу period cross over other one but not is the other period entirely inside the checking period. – Nemo Jan 16 '21 at 13:14
In this case instead use this method too long, use a @query annotation, is more usefull to undestand your code, your mode works, just an advice. – Jhonatan S. Souza Oct 08 '22 at 15:00

Vikash Kumar · Answer 3 · 2021-07-31T17:09:23.450

You can also write a custom query using @Query

@Query(value = "from EntityClassTable t where yourDate BETWEEN :startDate AND :endDate")
public List<EntityClassTable> getAllBetweenDates(@Param("startDate")Date startDate,@Param("endDate")Date endDate);

Edit: For LocalDateTime I just tried this solution for filtering record based on date and type in spring JPA:

Page<MyEntity> findMyEntityByEntityDateBetweenAndType(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate, @Param("type") String type, Pageable pageable);

Here EntityDate can be like CreatedDate,ModifiedDate etc.

how will it handle null start and end date? – saran3h Apr 01 '21 at 16:07 — saran3h, Apr 01 '21 at 16:07

score 3 · Answer 4 · answered Feb 09 '22 at 16:01

3

You can use JpaRepository

List<Entity> findByColumnDateBetween(LocalDateTime to, LocalDateTime from);

answered Feb 09 '22 at 16:01

RED-ONE

167
1
5

why in reverse order (to,from) instead of (from,to) ? – Ranjithkumar Aug 04 '22 at 10:42
5

@Ranjithkumar maybe because some people love dancing moonwalk ;) – BendaThierry.com Aug 20 '22 at 09:34

score 1 · Answer 5 · answered Nov 27 '20 at 07:00

1

Maybe you could try

List<Article> findAllByPublicationDate(Date publicationDate);

The detail could be checked in this article:

https://www.baeldung.com/spring-data-jpa-query-by-date

answered Nov 27 '20 at 07:00

Wu Yaozu

57
2

7

This is a combination of unexplained code and a link. Please add an explanation of how the code works and how it is supposed to solve the problem. Without such an explanation, your post spreads the misconception that StackOverflow is a platform for peddling unpaind programming work. Linking externally is not considered such an explanation. Link only answer might get deleted. – Yunnosch Nov 27 '20 at 07:53

Check date between two other dates spring data jpa

5 Answers5

Linked

Related