zq29 Posted November 27, 2006 Share Posted November 27, 2006 I have a books table that holds information about hotel bookings, I am trying to pull out all records where the client is due to leave their hotel room within 5 days. I'm string the start day, month and year in separate fields and the duration in a field. Here's what I have so far, although it doesn't appear to be pulling the correct rows - It is returning data, but I can't figure out why it's returning what it is. I assume I'm doing this wrong, so any chance someone could take a look at my query?[code]SELECT * FROM `booking` WHERE DATE_ADD(CURDATE(),INTERVAL 5 DAY) <= DATE_ADD(CONCAT(`start_date_y`,'-',`start_date_m`,'-',`start_date_d`),INTERVAL `nights` DAY)[/code] Link to comment https://forums.phpfreaks.com/topic/28627-slightly-more-advanced-date-calculations/ Share on other sites More sharing options...
fenway Posted November 27, 2006 Share Posted November 27, 2006 First, I'm confused why you have date_add on both sides... second, I didn't realize you could use a column as your interval value. Link to comment https://forums.phpfreaks.com/topic/28627-slightly-more-advanced-date-calculations/#findComment-131004 Share on other sites More sharing options...
zq29 Posted November 27, 2006 Author Share Posted November 27, 2006 I've got DATE_ADD on both sides as I want to compare 5 days from the current date, to a start date plus a duration (to form the end date).I'm not as knowledgeable in MySQL, so I just tried adding in a column as an interval value, and it didn't spit out any error messages so I guess it's valid... Although there is an error somewhere as it's not returning the expected results - Maybe it [b]is[/b] invalid to use a column as an interval and thats why my results are unexpected? Link to comment https://forums.phpfreaks.com/topic/28627-slightly-more-advanced-date-calculations/#findComment-131019 Share on other sites More sharing options...
zq29 Posted November 28, 2006 Author Share Posted November 28, 2006 Ok, just an update. My original query I was using up there in my original post was actually almost correct, I just had a <= instead of a >= as my comparator - That's why I was getting unexpected results, oops! Link to comment https://forums.phpfreaks.com/topic/28627-slightly-more-advanced-date-calculations/#findComment-131536 Share on other sites More sharing options...
fenway Posted November 29, 2006 Share Posted November 29, 2006 Glad you got it working... FYI, you won't be able to utilize any index on this query. Link to comment https://forums.phpfreaks.com/topic/28627-slightly-more-advanced-date-calculations/#findComment-132084 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.