SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with date and INTERVAL

    Hi all.

    I have problem with this query.
    In my table mysql `tbl_d` the value of the field `myDate` is 2011-05-23.

    Code:
    SELECT d.LOCK, d.myDate, x.dt
    FROM tbl_d AS d
    
    CROSS JOIN ( 
    SELECT 
    CASE 
    
    #case 1
    WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE) 
    AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) 
    > 0 THEN CURRENT_DATE
    
    #case 2
    WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -1) 
    AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) 
    > 0 THEN CURRENT_DATE - INTERVAL 1 MONTH
    
    #case 3
    WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -2) 
    AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) 
    > 0 THEN CURRENT_DATE - INTERVAL 2 MONTH
    
    #last case
    ELSE CURRENT_DATE - INTERVAL 3 MONTH
    END AS dt 
    FROM tbl_d) AS x 
    
    WHERE 1 
    
    AND MONTH(d.myDate) = MONTH(x.dt) 
    AND YEAR(d.myDate) = YEAR(x.dt)
    
    GROUP BY d.LOCK
    ORDER BY d.LOCK ASC
    The sql debug:
    #case 1 ---> 5 = 7 false;
    #case 2 ---> 5 = 6 false;
    #case 3 ---> 5 = 5 true;

    Affected rows: 0
    Time: 0.110ms

    Why? Always in the #last case?
    Thanks in advance, any help is very appreciated.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    the value of the field `myDate` is 2011-05-23
    Because we're in july (month 7) and 7 - 2 = 5 ?

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, I don't understand... :S

    7 - 2 = 5 ... ok, are in #case 3 ( 5=5 )?

    Why affected rows: 0 ?

    If no exists a date with the current month, I need extend the time interval up to find a valid date...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have this --
    Code:
    MONTH(CURRENT_DATE - 1)
    and i'm pretty sure you mean this --
    Code:
    MONTH(CURRENT_DATE) - 1
    check for yourself, just run this query --
    Code:
    SELECT CURRENT_DATE
         , MONTH(CURRENT_DATE) AS mm
         , MONTH(CURRENT_DATE - 1) AS x
         , MONTH(CURRENT_DATE) - 1 AS y
    what do you get for x and y?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I need an reading update on MySQL ... many thanks for your very appreciated help.
    Regards.

    Code:
    CURRENT_DATE	mm	x	y	LOCK	dt
    2011-07-04	7	7	6	1	2011-05-04


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
  •