CODEWITHSUNDEEP
mysqldate
Gadget Smith
Gadget Smith

Reputation: 25

Selecting between dates in mysql

Need to track and bill delivers based on date, for example if a deliver came in After 01/18/2010 but before 02/11/2010 it would be billed to Job no. 198.

I pretty much know it would be:

SELECT `no` FROM `jobs` WHERE 'start_date' >'2010-01-18' AND <`2010-02-11`;

in order to get '2010-01-18' AND < '2010-02-11', I have to look in the data base of course that defeats the purpose

I am given the Var=$delivery_date

And I am stuck right here.
How can I get the between dates without looking each time.

Sample of data base 

no    Start Date
198   2010 01 18  14:35    
199   2010 02 11  12:10   
200   2010 03 07  12:33    
201   2010 03 31  17:35   
202   2010 05 25  05:05     
203   2010 06 20  01:05    
204   2010 07 14  08:50   
205   2010 07 21  11:31    
206   2010 09 07  03:47

I hope I explained it well enough. I look at the manual and other questions but it always seems like they only have one date that is a variable and all of mine are

PS changing the format or method of the tables is not an option unless you can point me towards the time travel section

Upvotes: 1

Views: 67

Answers (3)

Gadget Smith
Gadget Smith

Reputation: 25

thanks to ekad this worked perfectly

SELECT no FROM jobs WHERE start_date < $delivery_date ORDER BY start_date DESC LIMIT 1

after the query the job no i need is first one

Upvotes: 0

Olli
Olli

Reputation: 1738

You can use the following select statement:

SELECT * FROM jobs WHERE date(start_date) BETWEEN "2010-02-08" AND "2010-03-15";

so you only take the date portion into account, not the time.

so with your date format it would be something like

SELECT * FROM jobs WHERE date(STR_TO_DATE(start_date, '%Y %m %d %h:%i')) BETWEEN "2010-02-08" AND "2010-03-15"

Upvotes: 1

ekad
ekad

Reputation: 14624

Looks like you want to find a jobs record with the largest start_date that's also less than $delivery_date. Here's the query

SELECT no 
FROM jobs 
WHERE `start_date` < $delivery_date
ORDER BY `start_date` DESC LIMIT 1

If $delivery_date is set to 2010-02-08, the above query will return 198 like you expected.

If $delivery_date is set to 2010-03-15, the above query will return 200.

Upvotes: 0

Related Questions