[RESOLVED] Query between curdate -1 and curdate +30 not working

dmacman1962

Hi Everyone,

I have some calendar code that stores the events in Month, Day, Year, start_time, and end_time columns and I want to select all dates between the current date - day and the current date + 30 days. (to show a 31 day list of events).

I have 2 dates in the query (OrderDate, EventDate), since there may be more than one event on the same day and, I want to order them ASC to get the correct order.

I have this query, which is errors with "Unknown Column 'EventDate' in 'where clause'".

SELECT CONCAT(m, '-', d, '-', y, ' ', start_time) as OrderDate,
CONCAT(m, '-', d, '-', y) as EventDate, id, start_time, end_time, title
FROM pec_mssgs
WHERE EventDate
BETWEEN (curdate() - INTERVAL 1 DAY) AND (CURDATE() + INTERVAL 1 DAY)
ORDER BY OrderDate ASC

I don't see where the issue is and I would appreciate any help and guidance.

Regards,
Don

Piranha

The real question is why you don't use a datetime column instead of several columns for the same information. That would be the right way to do it.

About your query: You can't use a alias in the WHERE-part of a query. Either you have to do the whole concat again or move it to the HAVING part of the query. Or, even better, use a datetime column 😉

dmacman1962

Yes, I kind of figured that was going to be the answer.

I will have to change the calendar code to a true datetime column. I dont know who wrote this, just implemented it instead of trying to write one myself.

Thanks for the info on using an alias in the where, I wasn't aware of that.

I appreciate the help.

Don

dmacman1962

OK, I changed the calendar code to store the date as '2009-3-22' (so I can compare it with CURDATE command in MySQL) and I am trying to select the date range, and I cannot get it to work:

SELECT CONCAT(date, ' ', start_time) as OrderDate, date, id, start_time, end_time, title
FROM pec_mssgs
WHERE date BETWEEN (curdate()-1) AND (CURDATE()+30)
ORDER BY OrderDate ASC

If I comment out the where, I can get all the dates, so I know its the WHERE that is not working.

Does anyone see the issue with my BETWEEN code?

Thanks,
Don

Lars_Berg

WHERE date BETWEEN current_date - interval '1' day AND current_date + interval '30' day

dmacman1962

Thanks, that worked perfectly.

Don