CODEWITHSUNDEEP
mysqldatesearch
test player
test player

Reputation: 331

Search by date range in multiple columns

I have a table with 4 columns — all dates. For example, insurance policy: you have 4 payment dates during the year — 2015-02-01, 2015-05-01, 2015-08-01, 2015-11-01.
How can I make a search within a date period? Let's say I have tons of data and lots of policies with 4 dates. I want to search from 2015-03-01 to 2015-03-31 and I want to see all the pending payments that fall in this period. I can't make it work for more than one column or to search by exact date.

This is a test template. I want to make a macro to do this: what do u want to search: pick from a dropdown menu - payment or end date If end date picked - look only in G, if payment picked - look in H, J and L Enter search date from: to: Pick a date period Button Click - see the results that answears my search parameters. I don't know how to do that?! :( http://dox.bg/files/dw?a=b99921ddc1

Upvotes: 1

Views: 1306

Answers (3)

Maxqueue
Maxqueue

Reputation: 2444

Lets say your four columns are col1, col2... Lets also say you are searching from 01/01/2014 to 01/31/2014

select * from [tablename] 
where col1>01/01/2014 
   or col1<01/31/2014 
   or col2>01/01/2014 
   or col2<01/31/2014 
   or col3>01/01/2014 
   or col3<01/31/2014 
   or col4>01/01/2014 
   or col4<01/31/2014

Hope this helps

Upvotes: 0

Armaan Dhir
Armaan Dhir

Reputation: 128

Make sure your datatype for the columns is of date type.
Try this:

SELECT * from tablename   
WHERE date_column >= '01-03-2015'   
AND date_column <= '31-03-215'
;

You can also try this

SELECT * from tablename   
WHERE cast(date_column as date) >= '01-03-2015'   
AND cast(date_column as date) <= '31-03-215'
;

for comparisons with 4 dates you can do:

SELECT * from tablename   
WHERE ((date_column >= '01-03-2015')   
AND (date_column <= '31-03-215'))
OR ((date_column >= '01-05-2015')
AND (date_column <= '31-05-2015'))
;

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

I think you want an OR condition.

Assuming that you have a predicate that works on one column (if that's not the case, you need to show what you have so far, the format of the dates is odd, makes it look like you may be storing these values as VARCHAR rather than DATE.)

But assuming that you've got a predicate that works on one of the columns, e.g. 

SELECT ...
  FROM ... t
 WHERE t.payment1_date_col  is in specified range

Then you can repeat that same predicate on another column, and combine the predicates using a boolean OR operator.

SELECT ...
  FROM ... t
 WHERE t.payment1_date_col  is in specified range
    OR t.payment2_date_col  is in specified range
    OR t.payment3_date_col  is in specified range
    OR t.payment4_date_col  is in specified range

If any one of the four columns is in the specified range, the row will be returned.

As far as the actual condition to check whether a date value is within a range, if the columns are datatype DATE, you could use a query like:

SELECT ...
  FROM ... t
 WHERE t.payment1_date_col BETWEEN '2015-03-01' AND '2015-03-31'
    OR t.payment2_date_col BETWEEN '2015-03-01' AND '2015-03-31'
    OR t.payment3_date_col BETWEEN '2015-03-01' AND '2015-03-31'
    OR t.payment4_date_col BETWEEN '2015-03-01' AND '2015-03-31'

Upvotes: 0

Related Questions