Professional Web Applications Themes

find a date range between current date and 1 year ago - MySQL

hey i need to find a date range between the current date and the date one year ago based on the current date so far i have this sql code SELECT type, SUM(quantity), date FROM table WHERE date BETWEEN current_date() and ??????????????????????????? GROUP BY date, type ORDER BY date; i just need to know what i put in the ??????????????????????????? area Regards Kelvan...

  1. #1

    Default find a date range between current date and 1 year ago

    hey i need to find a date range between the current date and the date
    one year ago based on the current date

    so far i have this sql code

    SELECT type, SUM(quantity), date FROM table
    WHERE date BETWEEN current_date() and ???????????????????????????
    GROUP BY date, type
    ORDER BY date;

    i just need to know what i put in the ??????????????????????????? area

    Regards
    Kelvan

    Lord Guest

  2. #2

    Default Re: find a date range between current date and 1 year ago

    Lord Kelvan wrote: 

    One of the following should do what you need:

    PERIOD_ADD(P,N)
    ADDDATE(date,INTERVAL expr unit)
    ADDDATE(expr,days)

    http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_period-add

    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: find a date range between current date and 1 year ago

    On May 22, 4:19 pm, "J.O. Aho" <net> wrote: 

    thoes functions are good but they are for future dates not prior dates
    i have the current date and basically i need a dynamic sql script to
    take todays date and count back 1 year and get that date

    regards
    Kelvan

    Lord Guest

  4. #4

    Default Re: find a date range between current date and 1 year ago

    Lord Kelvan wrote: [/ref]
     

    mysql> select ADDDATE(CURRENT_DATE(),INTERVAL -31 DAY);
    +------------------------------------------+
    | ADDDATE(CURRENT_DATE(),INTERVAL -31 DAY) |
    +------------------------------------------+
    | 2007-04-21 |
    +------------------------------------------+
    1 row in set (0.00 sec)

    --

    //Aho
    J.O. Guest

  5. #5

    Default Re: find a date range between current date and 1 year ago

    On 22 May, 05:41, Lord Kelvan <com> wrote: 




    >
    > thoes functions are good but they are for future dates not prior dates
    > i have the current date and basically i need a dynamic sql script to
    > take todays date and count back 1 year and get that date
    >
    > regards
    > Kelvan[/ref]

    so try DATE_SUB

    Captain Guest

  6. #6

    Default Re: find a date range between current date and 1 year ago

    On May 22, 8:30 pm, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > so try DATE_SUB- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    date sub wont work it is for subtracting dates but i realised one
    night when i was trying to sleep that you should be able to use
    negative numbers in the functions thank you for your help

    Lord Guest

  7. #7

    Default Re: find a date range between current date and 1 year ago

    On 23 May, 22:15, Lord Kelvan <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > date sub wont work it is for subtracting dates but i realised one
    > night when i was trying to sleep that you should be able to use
    > negative numbers in the functions thank you for your help- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    DATE_SUB will work as it will return a date in the past for you to use
    in a compare!

    Captain Guest

  8. #8

    Default Re: find a date range between current date and 1 year ago

    yes and thats good but i want it to be dynamic so i dont have to touch
    it periodadd with a negtive number will be good thx for ppls help

    Lord Guest

  9. #9

    Default Re: find a date range between current date and 1 year ago

    On 24 May, 23:07, Lord Kelvan <com> wrote: 

    DATE_SUB with a positive number is the equivalent of ADDDATE with a
    negative number.

    Both are as dynamic as each other.

    PERIOD_ADD however does not return a date.

    Captain Guest

Similar Threads

  1. Replies: 5
    Last Post: April 24th, 06:46 PM
  2. Date range - first date earlier than today
    By Johnny in forum Macromedia ColdFusion
    Replies: 16
    Last Post: August 9th, 02:14 AM
  3. 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
  4. 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