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


I don't know if you can do it in "native" sql but if you have V4 you can use user functions and for your second question - use the PDF
Roy Lambert

I thought there was a way, I think im getting confused with Sybase,

Nice addition to V5 though

Will have to create a custom function I think...

PDF.. nice, try searching for date in it!. Actually I didnt even know there
was one.

Cheers
Clive.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:BDC9E791-2968-4FC0-B6F8-B43D3B47C850@news.elevatesoft.com...
> Clive
>
>
> I don't know if you can do it in "native" sql but if you have V4 you can
> use user functions and for your second question - use the PDF
> Roy Lambert
>

Clive


So why are you complaining - how long does it take to read through 749 selections

Seriously I'm having similar problems with WPTools just now. Trying to figure out what to enter in the pdf, on-line groups, OLH or on-line manuals to find what I want is very difficult.

Roy Lambert

Clive,

<< Select dateadd(month, 3, mydate) from mytable

to get mydate and add 3 months on

for example ? >>

Do you simply want to increment the month portion of the date, or do you
want to add X number of days to the date ?

<< 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. >>

We merge the DBISAM help into the Delphi help for context-sensitive lookups,
etc., hence the issue.  If you load the DBISAM help file independently of
the Delphi IDE, then it will only show the DBISAM stuff.

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes, add 3 months, I know with days you can just add integers onto the date

Even when I open the .hlp file directly it still contains the delphi stuff
?.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:BB1A74F6-08CC-415B-A27A-CA17ECDFBB4C@news.elevatesoft.com...
> Clive,
>
> << Select dateadd(month, 3, mydate) from mytable
>
> to get mydate and add 3 months on
>
> for example ? >>
>
> Do you simply want to increment the month portion of the date, or do you
> want to add X number of days to the date ?
>
> << 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. >>
>
> We merge the DBISAM help into the Delphi help for context-sensitive
> lookups, etc., hence the issue.  If you load the DBISAM help file
> independently of the Delphi IDE, then it will only show the DBISAM stuff.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

On this subject, it'd be nice to have _lots_ more built-in SQL
functions.  Custom functions are a solution of course, but they are a
bit of a hassle, particularly because they don't work when you paste
your SQL into DBSys.  I would guess that adding many custom functions
would not enlarge DBISAM substantially because mostly they are simple
operations in Delphi.  Of course I don't know how hard it is to add
built-in functions to DBISAM, but I can think of many that would be
useful.  Of course its much easier to look at MySQL's excellent
selection and crib:

http://dev.mysql.com/doc/refman/4.1/en/functions.html


  Oliver

"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,

<< Yes, add 3 months, I know with days you can just add integers onto the
date >>

Jeff was kind enough to post pretty much what I would have posted.  The hard
part is always adjusting for leap years and February.

<< Even when I open the .hlp file directly it still contains the delphi
stuff ?. >>

Really ?  That's really odd.  And the help file is still in the \help
subdirectory under the main DBISAM installation directory ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes, Definetly.
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.

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

Cheers
Clive.


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:84D1BF1A-FC8D-4CDB-AAA0-6047A2BD2C3C@news.elevatesoft.com...
> Clive,
>
> << Yes, add 3 months, I know with days you can just add integers onto the
> date >>
>
> Jeff was kind enough to post pretty much what I would have posted.  The
> hard part is always adjusting for leap years and February.
>
> << Even when I open the .hlp file directly it still contains the delphi
> stuff ?. >>
>
> Really ?  That's really odd.  And the help file is still in the \help
> subdirectory under the main DBISAM installation directory ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>