1

I have a table

id |value |date
-------------------
1  |2.8   |28-3-14
2  |2.9   |28-7-14
3  |3.9   |20-1-14

in this table i need to get the value of 21-3-14. but if value or object is not present for that then query get output of 20-1-14 directly without one by one search object by minus date by 1 day. if any one know about this please give me suggestion.

nafas
  • 5,283
  • 3
  • 29
  • 57
Priti
  • 69
  • 5

2 Answers2

0

You just need to sort by date

SELECT value FROM table WHERE date<='21-3-14' ORDER BY date DESC LIMIT 1;

Based on your table it should print:

2.8

Assuming the date 21-3-14 wasn't there, it should print:

3.9
nafas
  • 5,283
  • 3
  • 29
  • 57
0

try this, 

SELECT 
        * 
FROM 
        <tablename> 
WHERE 
        STR_TO_DATE(`date`,'%d-%m-%y') <= STR_TO_DATE('YOUR_DATE','%d-%m-%y')
ORDER BY 
        `date` DESC < LIMIT 1 >

It is recommended to store date in date format i.e. < yyyy-mm-dd >

you may refer,

PHP mysql insert date format

Community
  • 1
  • 1
Hytool
  • 1,358
  • 1
  • 7
  • 22