Select record based on date/time range based on multiple rows

Question

I have following table structure

id          date             time            discount
------------------------------------------------------
1         2015-08-10       09:30:00           50%
2         2016-04-15       14:00:00           30%
3         2018-01-25       18:15:00           75%
4         2018-11-19       11:45:00           45%

I want to query this table to find the discount amount on specific date;

expected result;

if I query for a date between 2015-08-10 09:30:00 - 2016-04-15 13:59:59 it should result 50%

for a date between 2016-04-15 14:00:00 - 2018-01-25 18:14:59, should result 30%

example:

Query to this table for date 2016-01-10 08:00:00 should result 50%

I have searched through SO & Google but couldn't find a solution for my case;

Tried a query for my example like

SELECT * FROM discounts WHERE date<='2016-01-10' AND time<='08:00:00'
ORDER BY date DESC LIMIT 1

but no luck!

Any help is really appreciated.

Daryoosh Falak-Rafat · Accepted Answer · 2018-10-30 21:50:04Z

1

You could use something like this for your table structure -

SELECT discount FROM discounts WHERE CAST(CONCAT(date, “ “, time) AS DATETIME) BETWEEN “2015-08-10 09:30:00” AND “2016-04-15 13:59:59”

You might be able to get away without casting the values but this will ensure your date and time values are correctly formatted.

However I would suggest storing the date and time values as a single standard datetime value. Is there a reason why you have it as two separate columns?

answered Oct 30, 2018 at 21:50

Daryoosh Falak-Rafat

113 bronze badges

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

1 Comment

Alyas Over a year ago

Thanks for input, however we don't want to pass two dates to check the in between, my original question is to get the discount on the base of given single date/time input.

Collectives™ on Stack Overflow

Select record based on date/time range based on multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related