been away for a while and am obviously rusty.
Please assume 'today' is Saturday.
I am trying to output the days on which a product is available either side of today. So it could be Thursday and Tuesday. I have tried using the following query to return the max(day_of_week) before today - which is in fact Friday but, it returns 'Monday'.
here's the table
CREATE TABLE `product_times` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`product_on_sale_id` bigint(20) NOT NULL,
`day_of_week` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
`time_of_day` time DEFAULT NULL,
`max_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
`spaces_sold` int(11) DEFAULT NULL,
`remaining_spaces` varchar(6) COLLATE utf8_unicode_ci DEFAULT NULL,
`on_off` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_on_sale_id` (`product_on_sale_id`,`day_of_week`,`time_of_day`),
CONSTRAINT `productTimes_productsOnSale_fk` FOREIGN KEY (`product_on_sale_id`) REFERENCES `products_on_sale` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1985 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
and the query
from product_times as pt
join products_on_sale as pos
on pos.id = pt.product_on_sale_id
and pos.product_range_id = 199
where pt.day_of_week < 'Saturday'
I can't seem to work out what I think should be easy.
DATEPART(WEEKDAY, pt ) < 5
hop this help
you can hop all you want, this does not help at all
first, it's been months since the question was asked, and it's unlikely that bazz is still sitting there, waiting for an answer
second, DATEPART is not even a mysql function, and bazz is using mysql, which you might have discovered if you had bothered to read his entire post