CODEWITHSUNDEEP
phpmysqldatedatetime
mks
mks

Reputation: 52

Get Value of a date from mysql php

I am working with a travel software and i have to retrieve the rate of a hotel in a particular date.Hotel dates are added between three months(09/01/2015 to 12/31/2015). My database have two columns start date and enddate both are varchar type.The date format is mm/dd/yyyy.I have to retrieve value of single date (11/25/2015). Currently the query i am using is 

SELECT * 
FROM tbl_roomtype 
where `room_id`='17' 
  and roomoccupancy='Double' 
  AND startdate<='11/25/2015' 
  and enddate >='11/25/2015'

The query is not working perfectly.So anybody can suggest a solution.

Upvotes: 1

Views: 317

Answers (2)

tino.codes
tino.codes

Reputation: 1507

You have to parse your strings to a valid date. To filter you have to use a string in the 'YYYY-MM-DD' format.

SELECT
    *
FROM
    `tbl_roomtype`
WHERE
    `room_id` = '17' 
    AND `roomoccupancy` = 'Double' 
    AND STR_TO_DATE(`startdate`, '%m/%d/%Y') <= '2015-11-25' 
    AND STR_TO_DATE(`enddate`, '%m/%d/%Y') >= '2015-11-25'

Or (if you not could fix your filter:

SELECT
    *
FROM
    `tbl_roomtype`
WHERE
    `room_id` = '17' 
    AND `roomoccupancy` = 'Double' 
    AND STR_TO_DATE(`startdate`, '%m/%d/%Y') <= STR_TO_DATE('11/25/2015', '%m/%d/%Y') 
    AND STR_TO_DATE(`enddate`, '%m/%d/%Y') >= STR_TO_DATE('11/25/2015', '%m/%d/%Y')

Upvotes: 1

Giles
Giles

Reputation: 1687

You need to use str_to_date (str_to_date). Try this:

SELECT * 
FROM tbl_roomtype 
where `room_id`='17' 
  and roomoccupancy='Double' 
  AND STR_TO_DATE(startdate,'%m/%d/%Y')<=STR_TO_DATE('11/25/2015' ,'%m/%d/%Y')
  and STR_TO_DATE(enddate,'%m/%d/%Y') >=STR_TO_DATE('11/25/2015' ,'%m/%d/%Y')

Upvotes: 2

Related Questions