string comparison question

[IBazz]

Hi,

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

  select max(day_of_week) 
    from product_times as pt
 inner 
     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.
bazz

[sieuraovat.net]

 DATEPART(WEEKDAY, pt ) < 5

hop this help

[r937]

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