Professional Web Applications Themes

query involves dates - MySQL

Hi, I am trying to get the following from a customer table: A list of all customers under 10 that have a birthday in the next week. I tried it and I think the first part is ok (unless I am mistaken!) but I don't really know how to do "under 10 that have a birthday in the next week". select first_name, last_name, dob from customer where dob < current_date + interval 10 year; Thanks in advance. Ros...

  1. #1

    Default query involves dates

    Hi,

    I am trying to get the following from a customer table:
    A list of all customers under 10 that have a birthday in the next
    week.

    I tried it and I think the first part is ok (unless I am mistaken!)
    but I don't really know how to do "under 10 that have a birthday in
    the next week".

    select first_name, last_name, dob
    from customer
    where dob < current_date + interval 10 year;

    Thanks in advance.
    Ros

    roohbir Guest

  2. #2

    Default Re: query involves dates

    On 3 Apr, 11:41, "roohbir" <com> wrote: 

    Well, since current_date + 10 years is in the future, anyone who has
    already been born satisfies that condition!

    The query below assumes that "in the next week", includes today.

    SELECT
    `first_name`,
    `last_name`,
    `dob`
    FROM `customer`
    WHERE `dob` > CURRENT_DATE - INTERVAL 10 YEAR
    AND `dob` BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY

    Captain Guest

  3. #3

    Default Re: query involves dates

    On 3 Apr, 14:30, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > Close - but it will only return those born in the next week (BETWEEN
    > CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY).
    >
    > I guess you could always "brute force" it - not pretty:
    >
    > SELECT `first_name`, `last_name`, `dob`
    > FROM `customer`
    > WHERE `dob` BETWEEN CURRENT_DATE - INTERVAL 10 YEAR AND CURRENT_DATE -
    > INTERVAL 10 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 9 YEAR AND CURRENT_DATE - INTERVAL
    > 9 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 8 YEAR AND CURRENT_DATE - INTERVAL
    > 8 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 7 YEAR AND CURRENT_DATE - INTERVAL
    > 7 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 6 YEAR AND CURRENT_DATE - INTERVAL
    > 6 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 5 YEAR AND CURRENT_DATE - INTERVAL
    > 5 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 4 YEAR AND CURRENT_DATE - INTERVAL
    > 4 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 3 YEAR AND CURRENT_DATE - INTERVAL
    > 3 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 2 YEAR AND CURRENT_DATE - INTERVAL
    > 2 YEAR + INTERVAL 7 DAY OR
    > `dob` BETWEEN CURRENT_DATE - INTERVAL 1 YEAR AND CURRENT_DATE - INTERVAL
    > 1 YEAR + INTERVAL 7 DAY;
    >
    > Too bad there isn't a month_day function in MySQL.
    >
    > I also played around with dayofyear. It works fine with two exceptions
    > - the last week of the year (which can be handled fairly easily), and
    > leap years. The latter is a problem because everything after Feb. 28th
    > will be off one day if either the current year or the dob year is a leap
    > year, but not both. The latter started getting very complicated to handle.
    >
    > Another possibility which I haven't tried (and may have some errors)
    > might be:
    >
    > SELECT `first_name`, `last_name`, `dob`
    > FROM `customer`
    > WHERE `dob` > CURRENT_DATE = INTERVAL 10 YEAR AND
    > `dob` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`dob`)) YEAR BETWEEN
    > CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
    >
    > But it should work
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Good point

    Captain Guest

  4. #4

    Default Re: query involves dates

    Captain Paralytic wrote: 
    >
    > Well, since current_date + 10 years is in the future, anyone who has
    > already been born satisfies that condition!
    >
    > The query below assumes that "in the next week", includes today.
    >
    > SELECT
    > `first_name`,
    > `last_name`,
    > `dob`
    > FROM `customer`
    > WHERE `dob` > CURRENT_DATE - INTERVAL 10 YEAR
    > AND `dob` BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
    >[/ref]

    Close - but it will only return those born in the next week (BETWEEN
    CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY).

    I guess you could always "brute force" it - not pretty:

    SELECT `first_name`, `last_name`, `dob`
    FROM `customer`
    WHERE `dob` BETWEEN CURRENT_DATE - INTERVAL 10 YEAR AND CURRENT_DATE -
    INTERVAL 10 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 9 YEAR AND CURRENT_DATE - INTERVAL
    9 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 8 YEAR AND CURRENT_DATE - INTERVAL
    8 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 7 YEAR AND CURRENT_DATE - INTERVAL
    7 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 6 YEAR AND CURRENT_DATE - INTERVAL
    6 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 5 YEAR AND CURRENT_DATE - INTERVAL
    5 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 4 YEAR AND CURRENT_DATE - INTERVAL
    4 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 3 YEAR AND CURRENT_DATE - INTERVAL
    3 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 2 YEAR AND CURRENT_DATE - INTERVAL
    2 YEAR + INTERVAL 7 DAY OR
    `dob` BETWEEN CURRENT_DATE - INTERVAL 1 YEAR AND CURRENT_DATE - INTERVAL
    1 YEAR + INTERVAL 7 DAY;

    Too bad there isn't a month_day function in MySQL.

    I also played around with dayofyear. It works fine with two exceptions
    - the last week of the year (which can be handled fairly easily), and
    leap years. The latter is a problem because everything after Feb. 28th
    will be off one day if either the current year or the dob year is a leap
    year, but not both. The latter started getting very complicated to handle.

    Another possibility which I haven't tried (and may have some errors)
    might be:

    SELECT `first_name`, `last_name`, `dob`
    FROM `customer`
    WHERE `dob` > CURRENT_DATE = INTERVAL 10 YEAR AND
    `dob` + INTERVAL (YEAR(CURRENT_DATE) - YEAR(`dob`)) YEAR BETWEEN
    CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY

    But it should work



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: query involves dates

    Jerry Stuckle <net> wrote in
    news:com:

     

    But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')

    --
    felix
    Felix Guest

  6. #6

    Default Re: query involves dates

    On 3 Apr, 16:03, Felix Geerinckx <com> wrote: 
    >
    > But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')
    >
    > --
    > felix[/ref]

    Yes, but it is no help in this case as 1230 will be greater than 0101.

    Captain Guest

  7. #7

    Default Re: query involves dates

    On Apr 3, 8:22 am, "Captain Paralytic" <com> wrote: 
    > [/ref]


    >
    > Yes, but it is no help in this case as 1230 will be greater than 0101.[/ref]

    Thanks folks. That fixed the problem.
    Cheers
    ros

    ros Guest

  8. #8

    Default Re: query involves dates

    "Captain Paralytic" <com> wrote in
    news:googlegroups.com:
     
    >>
    >> But there is: DATE_FORMAT(CURRENT_DATE, '%m%d')
    >>
    >> --
    >> felix[/ref]
    >
    > Yes, but it is no help in this case as 1230 will be greater than 0101.
    >[/ref]

    But it allows you to easily calculate someone's age at a certain date:

    SELECT
    YEAR(adate) - YEAR(dob) -
    (DATE_FORMAT(adate, '%m%d') < DATE_FORMAT(dob, '%m%d')) AS Age
    FROM ...


    --
    felix
    Felix Guest

Similar Threads

  1. query involves cases
    By roohbir in forum MySQL
    Replies: 2
    Last Post: April 3rd, 08:37 PM
  2. Help with query using dates
    By Ruckus50 in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 31st, 10:07 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139