Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 21 to 30 of 32 total |
Syntax for date calculation |
Thu, May 31 2012 6:27 AM | Permanent Link |
John Hay | Roy
> > >SELECT datefield+CAST(integervalue (or integerfield) AS INTERVAL MONTH) AS NewDateField FROM Table > > That works fine until you want to make it a parameter rather than a constant or field > Indeed. As I understand it this is a restriction on parameters being used as result columns (or column modifiers), not anything to do with Intervals as such. John |
Thu, May 31 2012 8:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>Indeed. As I understand it this is a restriction on parameters being used as result columns (or column modifiers), not >anything to do with Intervals as such. Dunno, I'd have to dig out Tim's post about it. Its enough for me to know that it doesn't work which is a shame because most of my use for INTERVALs is in sql where I would use parameters. Roy |
Thu, May 31 2012 9:30 AM | Permanent Link |
John Hay | Roy
> Dunno, I'd have to dig out Tim's post about it. Its enough for me to know that it doesn't work which is a shame because most of my use for INTERVALs is in sql where I would use parameters. > .... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine. John |
Thu, May 31 2012 10:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine. Nice one - after I figured out how to set the parameter type in EDBManager Roy Lambert |
Fri, Jun 1 2012 7:46 AM | Permanent Link |
Uli Becker | John,
> ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine. SCRIPT BEGIN Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS NewDateField FROM Kalender' using 2; END Does *not* work: ElevateDB Error #700 An error was found in the statement at line 3 and column 19 (Invalid expression ? found, dynamic parameter references not allowed) Uli |
Fri, Jun 1 2012 9:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
>> ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine. > >SCRIPT >BEGIN > Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS >NewDateField FROM Kalender' using 2; >END > >Does *not* work: Try this instead SCRIPT(IN FRED INTEGER) BEGIN DECLARE STMT VARCHAR; SET STMT = 'SELECT _dateadded + INTERVAL ''' + CAST(fred AS VARCHAR)+''' MONTH AS NewDateField FROM calls'; Execute Immediate STMT; END You have to write the whole statement. Roy Lambert [Team Elevate] |
Fri, Jun 1 2012 11:21 AM | Permanent Link |
Uli Becker | Roy,
> Try this instead > > SCRIPT(IN FRED INTEGER) > BEGIN > DECLARE STMT VARCHAR; > SET STMT = 'SELECT _dateadded + INTERVAL ''' + CAST(fred AS VARCHAR)+''' MONTH AS NewDateField FROM calls'; > Execute Immediate STMT; > END > > You have to write the whole statement. That's what I wrote to Sorin at the beginning of this thread. I just wanted to point out that John's solution withe a "real" parameter does not work. Uli |
Mon, Jun 4 2012 4:58 AM | Permanent Link |
John Hay | "Uli Becker" <johnmuller54@googlemail.com> wrote in message news:A6647C79-773B-412A-9BA0-EFBA7B5E2E2E@news.elevatesoft.com... > John, > > > ... adate+CAST(:months AS INTERVAL MONTH) ... in the where clause should be fine. > > SCRIPT > BEGIN > Execute Immediate 'SELECT Datum + CAST(? AS INTERVAL MONTH) AS > NewDateField FROM Kalender' using 2; > END > > Does *not* work: I agree It doesn't work - and is not what I suggested The point is that it is nothing to do with intervals, but is about using paramters in the select list. I am guessing the following (which is what I suggested) will work SCRIPT BEGIN Execute Immediate 'SELECT * FROM Kalender WHERRE Datum + CAST(? AS INTERVAL MONTH) > CURRENT_DATE ' using 2; END John |
Mon, Jun 4 2012 5:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
Seems to when you change WHERRE to the US rather than Scottish spelling Roy Lambert [Team Elevate] |
Mon, Jun 4 2012 6:28 AM | Permanent Link |
John Hay | Roy
> > Seems to when you change > > WHERRE > LOL - just back from a 50th birthday weekend bash.... John |
« Previous Page | Page 3 of 4 | Next Page » |
Jump to Page: 1 2 3 4 |
This web page was last updated on Saturday, June 22, 2024 at 05:51 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |