SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    string comparison question

    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

    Code MySQL:
    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

    Code MySQL:
      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

  2. #2
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     DATEPART(WEEKDAY, pt ) < 5
    hop this help

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sieuraovat.net View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •