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 Kelvan wrote:
> 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

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/...ion_period-add

--

//Aho

On May 22, 4:19 pm, "J.O. Aho" <u...@example.net> 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/...nctions.html#f...
>
> --
>
> //Aho

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 Kelvan wrote:
> On May 22, 4:19 pm, "J.O. Aho" <u...@example.net> 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/...nctions.html#f...

> 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

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

On 22 May, 05:41, Lord Kelvan <the_idd...@hotmail.com> wrote:
> On May 22, 4:19 pm, "J.O. Aho" <u...@example.net> 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/...nctions.html#f...
>
> > --
>
> > //Aho
>
> 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

so try DATE_SUB

On May 22, 8:30 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 22 May, 05:41, Lord Kelvan <the_idd...@hotmail.com> wrote:
>
>
>
>
>
> > On May 22, 4:19 pm, "J.O. Aho" <u...@example.net> 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/...nctions.html#f...
>
> > > --
>
> > > //Aho
>
> > 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
>
> so try DATE_SUB- Hide quoted text -
>
> - Show quoted text -

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

On 23 May, 22:15, Lord Kelvan <the_idd...@hotmail.com> wrote:
> On May 22, 8:30 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 22 May, 05:41, Lord Kelvan <the_idd...@hotmail.com> wrote:
>
> > > On May 22, 4:19 pm, "J.O. Aho" <u...@example.net> 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/...nctions.html#f...
>
> > > > --
>
> > > > //Aho
>
> > > 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
>
> > so try DATE_SUB- Hide quoted text -
>
> > - Show quoted text -
>
> 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 -

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

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

On 24 May, 23:07, Lord Kelvan <the_idd...@hotmail.com> wrote:
> 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

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.