Very clever Jeff, I wouldnt have thought of that, the leap year would be a
problem though as my dates can span many years and I have to ensure the
periods always fall on the 1st of the month..

Im going to probably go with a custom function.

Cheers anyway

Clive.

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:18D1FDB5-28F5-4135-BED2-3785D9FB5661@news.elevatesoft.com...
> "Clive" <dd@dddd.com> wrote on Wed, 12 Apr 2006 16:22:55 +1200
>
>>is it possible to add months in SQL?
>>
>>ie
>>
>>Select dateadd(month, 3, mydate) from mytable
>>
>>to get mydate and add 3 months on
>>
>>for example ?
>>
>>I searched the NG and the help file but couldnt find anything, BTW how do
>>you set the DBISAM help file to ONLY show dbisam related stuff, mine shows
>>all the delphi stuff as well which makes searching the help file very
>>difficult.
>>
>>Thanks
>>Clive
>>
>>
> Clive
>
>
> If you are always adding 3 months then the following code will work most
> of the time. For the 3 months prior to the 29th of Feb in a leap year it
> will be one day short. That may not matter in many circumstances - e.g.
> making diary items for 3 months hence - two sevenths of the time it will
> hit a weekend anyway and have to be adjusted.
>
> HTH
>
> Cheers
>
> Jeff
> ========================================
> SELECT MyDate,
> IF(EXTRACT(MONTH FROM MyDate) = 1 THEN MyDate + 90
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 2 THEN MyDate + 89
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 3 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 4 THEN MyDate + 91
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 5 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 6 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 7 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 8 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 9 THEN MyDate + 91
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 10 THEN MyDate + 92
> ELSE IF(EXTRACT(MONTH FROM MyDate) = 11 THEN MyDate + 92
> ELSE MyDate + 90 ))))))))))) AS ThreeMonthsOn
> FROM MyTable
> WHERE NOT MyDate IS NULL
> =========================================
>
> Actual code would look like:-
> UPDATE MyTable
> SET MyDate = .........................................
> WHERE NOT MyDate IS NULL
>
>
> --
> Jeff Cook
> Aspect Systems Ltd
> Phone: +64-9-424 5388
> Skype: jeffcooknz
> www.aspect.co.nz
>
>
>
>

"Clive" <dd@dddd.com> wrote in
news:63990E42-C9E7-4EDA-90D1-794FF38383CB@news.elevatesoft.com:

> Im going to probably go with a custom function as I have to ensure my
> periods always fall on the 1st of the month.
>
Clive,

 I'm a little late joining this thread, but you could try :

SELECT MyDateField, CAST( CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT
(YEAR, MyDateField),EXTRACT(YEAR, MyDateField)+1) AS CHAR(4))+'-'+
                 CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT(MONTH,
MyDateField)+3, EXTRACT(MONTH, MyDateField)-9) AS CHAR(2))+'-01' AS DATE)
FROM MyTable

if you're pushed !

--
Chris

Perfect I think!..

Cheers
Clive.
"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97A55FB29629D14torcatis@64.65.248.118...
> "Clive" <dd@dddd.com> wrote in
> news:63990E42-C9E7-4EDA-90D1-794FF38383CB@news.elevatesoft.com:
>
>> Im going to probably go with a custom function as I have to ensure my
>> periods always fall on the 1st of the month.
>>
> Clive,
>
>  I'm a little late joining this thread, but you could try :
>
> SELECT MyDateField, CAST( CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT
> (YEAR, MyDateField),EXTRACT(YEAR, MyDateField)+1) AS CHAR(4))+'-'+
>                  CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT(MONTH,
> MyDateField)+3, EXTRACT(MONTH, MyDateField)-9) AS CHAR(2))+'-01' AS DATE)
> FROM MyTable
>
> if you're pushed !
>
> --
> Chris

Perfect I think!.. Many thanks.
Cheers
Clive.
"Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
news:Xns97A55FB29629D14torcatis@64.65.248.118...
> "Clive" <dd@dddd.com> wrote in
> news:63990E42-C9E7-4EDA-90D1-794FF38383CB@news.elevatesoft.com:
>
>> Im going to probably go with a custom function as I have to ensure my
>> periods always fall on the 1st of the month.
>>
> Clive,
>
>  I'm a little late joining this thread, but you could try :
>
> SELECT MyDateField, CAST( CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT
> (YEAR, MyDateField),EXTRACT(YEAR, MyDateField)+1) AS CHAR(4))+'-'+
>                  CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT(MONTH,
> MyDateField)+3, EXTRACT(MONTH, MyDateField)-9) AS CHAR(2))+'-01' AS DATE)
> FROM MyTable
>
> if you're pushed !
>
> --
> Chris

Clive,

<< Although just played around with it and if you rename the .CNT, .FTG,
..GID files the help file comes up with just dbisam stuff. (No contents of
course). But if I include those files it always brings up all the Delphi
help combined. >>

Crap, I forgot that we hard-code the links to Delphi's help in the .CNT
file.  If you look at the .CNT file with notepad, you will see where we link
the two - removing any references to the Delphi help will prevent the
showing of the Delphi stuff.

<< Im going to probably go with a custom function as I have to ensure my
periods always fall on the 1st of the month. >>

Ahh, I didn't know that the day was always the first of the month.  That's
easy, as Chris has pointed out.  It's a problem, however, when the day falls
near the end of the month.

--
Tim Young
Elevate Software
www.elevatesoft.com

Works great for additional 12 months or less, What If I wanted to add 26
Months for example ?. The number of months to add is user defined so they
can type anything.

Back to the custom function?..

"Clive" <dd@dddd.com> wrote in message
news:95EFFD45-C61B-4B20-9C62-920E61B33DCE@news.elevatesoft.com...
> Perfect I think!.. Many thanks.
> Cheers
> Clive.
> "Chris Erdal" <chris@No-Spam-erdal.net> wrote in message
> news:Xns97A55FB29629D14torcatis@64.65.248.118...
>> "Clive" <dd@dddd.com> wrote in
>> news:63990E42-C9E7-4EDA-90D1-794FF38383CB@news.elevatesoft.com:
>>
>>> Im going to probably go with a custom function as I have to ensure my
>>> periods always fall on the 1st of the month.
>>>
>> Clive,
>>
>>  I'm a little late joining this thread, but you could try :
>>
>> SELECT MyDateField, CAST( CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT
>> (YEAR, MyDateField),EXTRACT(YEAR, MyDateField)+1) AS CHAR(4))+'-'+
>>                  CAST(IF(EXTRACT(MONTH, MyDateField)<=9,EXTRACT(MONTH,
>> MyDateField)+3, EXTRACT(MONTH, MyDateField)-9) AS CHAR(2))+'-01' AS DATE)
>> FROM MyTable
>>
>> if you're pushed !
>>
>> --
>> Chris
>
>
>

Clive,

<< Back to the custom function?.. >>

Yes.  When you start throwing in incrementing years, it is much easier to
deal with in code.

--
Tim Young
Elevate Software
www.elevatesoft.com

The custom function cabability is very powerfull, its really only 2 lines to
achieve this

 if (lowercase(FunctionName) = 'incmonth') then
   result := IncMonth( FunctionParams[0].AsDateTime,
FunctionParams[1].AsInteger );




"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:75E77922-A06D-48F9-9B8D-6AA847665C13@news.elevatesoft.com...
> Clive,
>
> << Back to the custom function?.. >>
>
> Yes.  When you start throwing in incrementing years, it is much easier to
> deal with in code.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

"Clive" <dd@dddd.com> wrote in message
news:A8832443-1C6E-450B-A902-F07D4F8E1FFE@news.elevatesoft.com...
> The custom function cabability is very powerfull, its really only 2 lines
> to achieve this
>
>  if (lowercase(FunctionName) = 'incmonth') then
>    result := IncMonth( FunctionParams[0].AsDateTime,
> FunctionParams[1].AsInteger );
>

select ID, adate olddate, 26 - (round(26 / 12) * 12) + extract(month from
adate) newmonth, round(26 / 12) + extract(year from adate) newyear
into memory\temp
from atable;
update memory\temp set newmonth = newmonth + 12, newyear = newyear - 1 where
newmonth < 1;
update memory\temp set newmonth = newmonth - 12, newyear = newyear + 1 where
newmonth > 12;
select * from memory temp;

The "26" would of course be a parameter. You need to select some ID field,
then JOIN the memory table to the disk table for the update. It can probably
be done all in one step, but it is late I think it should work with any
positive value of number of months. Might also work with negatives, did not
think it thru.

Robert

>
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
> news:75E77922-A06D-48F9-9B8D-6AA847665C13@news.elevatesoft.com...
>> Clive,
>>
>> << Back to the custom function?.. >>
>>
>> Yes.  When you start throwing in incrementing years, it is much easier to
>> deal with in code.
>>
>> --
>> Tim Young
>> Elevate Software
>> www.elevatesoft.com
>>
>>
>
>

Cheers Robert.. My mind went numb reading it!.

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:F091B32F-0CAA-45B0-BA8B-A3ADB097E14D@news.elevatesoft.com...
>
> "Clive" <dd@dddd.com> wrote in message
> news:A8832443-1C6E-450B-A902-F07D4F8E1FFE@news.elevatesoft.com...
>> The custom function cabability is very powerfull, its really only 2 lines
>> to achieve this
>>
>>  if (lowercase(FunctionName) = 'incmonth') then
>>    result := IncMonth( FunctionParams[0].AsDateTime,
>> FunctionParams[1].AsInteger );
>>
>
> select ID, adate olddate, 26 - (round(26 / 12) * 12) + extract(month from
> adate) newmonth, round(26 / 12) + extract(year from adate) newyear
> into memory\temp
> from atable;
> update memory\temp set newmonth = newmonth + 12, newyear = newyear - 1
> where newmonth < 1;
> update memory\temp set newmonth = newmonth - 12, newyear = newyear + 1
> where newmonth > 12;
> select * from memory temp;
>
> The "26" would of course be a parameter. You need to select some ID field,
> then JOIN the memory table to the disk table for the update. It can
> probably be done all in one step, but it is late I think it should
> work with any positive value of number of months. Might also work with
> negatives, did not think it thru.
>
> Robert
>
>>
>>
>>
>> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
>> message news:75E77922-A06D-48F9-9B8D-6AA847665C13@news.elevatesoft.com...
>>> Clive,
>>>
>>> << Back to the custom function?.. >>
>>>
>>> Yes.  When you start throwing in incrementing years, it is much easier
>>> to deal with in code.
>>>
>>> --
>>> Tim Young
>>> Elevate Software
>>> www.elevatesoft.com
>>>
>>>
>>
>>
>
>