How to select data between dates where the checkin and checkout field type is in varchar?

Question

I want to select data between dates from a table

for eg:guestrocordtransac(tbl name)

id,roomno,roomtype, checkin,       checkout
 1  101      Baywatch  05/17/2015 05/24/2015
 2  102      BayWatch  05/10/2015 05/16/2015

when i tried writing query like this i get only one roomno as 101

SELECT roomno FROM guestrocordtransac where checkin between "05/14/2015" and "05/29/2015"

score 4 · Answer 1 · answered May 15 '15 at 01:32

4

The correct answer is to fix your data so the date values are stored as date/times. It is almost always better to use native database types for values.

The second best thing (and it is a very remote second best) is to do the conversion on the fly:

SELECT roomno
FROM guestrocordtransac
where str_to_date(checkin, '%m/%d/%Y') between '2015-05-14' and '2015-05-29';

When using date constants, use the ISO standard format of YYYY-MM-DD.

answered May 15 '15 at 01:32

Gordon Linoff

1,242,037
58
646
786

no its not working....My data stored not as date it is as varchar... – user12688 May 15 '15 at 01:38
@ram this solution is because your data is stored as a varchar or string instead of a date.... Show the error message or result if it doesn't work – Devon Bessemer May 15 '15 at 01:40
ram - say why it's not working: Wrong results? Error message? Hotel room caught fire? – Ed Gibbs May 15 '15 at 01:41
if its a date format field then how can i insert my date as m/d/Y formate......since the date format is 0000:00:00 – user12688 May 15 '15 at 01:43
i need it for dispaly – user12688 May 15 '15 at 01:44
@ram . . . Then use `date_format()` to format the value when you display it. – Gordon Linoff May 15 '15 at 01:46

How to select data between dates where the checkin and checkout field type is in varchar?

1 Answers1