Professional Web Applications Themes

URGENT HELP NEEDED PLEASE! Selecting a date range without the year - MySQL

Hi, I need to be able to select a range of dates between cretain months and days but without the year, Something like : SELECT foobar FROM footable WHERE dates BETWEEN start_date_month_day AND end_date_month_day That will return all the results between those months and days regardless of the year. Any help would be appreciated. Thanks, paul....

  1. #1

    Default URGENT HELP NEEDED PLEASE! Selecting a date range without the year

    Hi,
    I need to be able to select a range of dates between cretain months
    and days but without the year,

    Something like :

    SELECT foobar FROM footable WHERE dates BETWEEN start_date_month_day
    AND end_date_month_day


    That will return all the results between those months and days
    regardless of the year.


    Any help would be appreciated.

    Thanks, paul.

    macca Guest

  2. #2

    Default Re: URGENT HELP NEEDED PLEASE! Selecting a date range without the year

    macca wrote: 

    Can the dates span year boundaries?


    Paul Guest

  3. #3

    Default Re: URGENT HELP NEEDED PLEASE! Selecting a date range without the year

    Yes.

    Ive figured it out now though thanks, i used

    WHERE (MONTH(dates) BETWEEN month1 AND month2)


    thanks anyway

    macca Guest

  4. #4

    Default Re: URGENT HELP NEEDED PLEASE! Selecting a date range without the year

    On 23 Apr, 22:29, macca <com> wrote: 

    But does that work when month1 is November and month2 is January?

    Also you haven't included the day part?

    Captain Guest

  5. #5

    Default Re: URGENT HELP NEEDED PLEASE! Selecting a date range without the year

    Well i did, i just didnt show it here. The where clause of the query
    is:


    WHERE (MONTH(PurchaseDate) BETWEEN #form.txtStartMonth# AND
    #form.txtEndMonth#) AND (DAYOFMONTH(PurchaseDate) BETWEEN
    #form.txtStartDay# AND #form.txtEndDay#) AND (YEAR(PurchaseDate)
    BETWEEN #form.txtStartYear# AND #form.txtEndYear#) ORDER BY
    PurchaseDate ASC

    With each of DAYOFMONTH,MONTH & YEAR only being concatenated to the
    where clause if the user sets one in the form


    But you are right.This does not work when month1 is November and
    month2 is January. I didnt even think of this


    suggestion appreciated...


    macca Guest

  6. #6

    Default Re: URGENT HELP NEEDED PLEASE! Selecting a date range without theyear

    macca wrote: 

    Just check - if start month is > end month, ensure the purchase date is
    NOT between end month and start month.

    I'll leve the actual SQL up to you - but it's not much different than
    what you have :-)

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

Similar Threads

  1. Error Selecting Timestamps - String Index out of Range
    By FredPope in forum Coldfusion Database Access
    Replies: 0
    Last Post: October 31st, 04:41 PM
  2. Automatically selecting date range
    By chipjohns in forum Coldfusion Database Access
    Replies: 8
    Last Post: October 19th, 06:06 PM
  3. Selecting a range of dates in MySql
    By Jeff Roe in forum PHP Development
    Replies: 1
    Last Post: October 1st, 11:37 PM
  4. Very Slow when date range includes future date
    By Jacco in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 22nd, 06:20 AM
  5. Replies: 0
    Last Post: July 14th, 10:01 AM

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