2

I was reading through a couple of older posts and tried to apply the same logic to my question, I need to extract 13 months of data broken down per month, I would also like to apply the data to relevant headers... any suggestions. Please see code below and error received.

SELECT ST.TXDATE, ST.CODE, ST.QUANTITY    
FROM StocTran ST
WHERE ST.TXDATE >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE))
ORDER BY ST.TXDATE

ERROR: [Elevate Software][DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected end of statement but instead found ( in SELECT SQL statement at line 3, column 27 Error Code: 11949

Victoria
  • 7,822
  • 2
  • 21
  • 44
Griphon
  • 25
  • 3

1 Answers1

4

DATEADD is a function in MS's TransactSQL for Sql Server. I do not know that DBIsam supports it, and it is not listed in DBIsam's list of supported functions here:

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=functions

Generally, date functions are not portable across different SQL engines, and from that list, one possibility might be to use the EXTRACT function instead:

The EXTRACT function returns a specific value from a date, time, or timestamp value. The syntax is as follows:

EXTRACT(extract_value
        FROM column_reference or expression)
EXTRACT(extract_value,
        column_reference or expression)

Use EXTRACT to return the year, month, week, day of week, day, hours, minutes, seconds, or milliseconds from a date, time, or timestamp column. EXTRACT returns the value for the specified element as an integer.

The extract_value parameter may contain any one of the specifiers: YEAR MONTH WEEK DAYOFWEEK DAYOFYEAR DAY HOUR MINUTE SECOND MSECOND

Even if you are in a hurry, I strngly recommend that you study that page carefully.

UPDATE: From googling dbisam dateadd it looks like Elevate don't have a good answer for an equivalent to DATEADD. One of the hits is this thread:

https://www.sqlservercentral.com/Forums/Topic173627-169-1.aspx

which suggested an alternative way to do it using Delphi's built-in date functions (like IncMonth which I suggested you use in an answer to another q. Basically, you would calculate the start- and end-dates of a range of dates, then convert them to strings to construct a WHERE clause with a column date (from your db) which is equal to or greater than the start date and less or equal to the end date.

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • do you have any suggestions how to create a variable report using this data extract function, e.g.: EXTRACT(MONTH FROM SaleDate) AS MonthNo - 1, EXTRACT(MONTH FROM SaleDate) AS MonthNo -2 ... and so forth? – Griphon Jan 09 '18 at 11:57
  • No, because I don't have a copy of DBIsam to test it on. But basically it would involve constructing a WHERE clause equivalent to your DATEADD one. The people to ask about this are DBIsam's publishers, Elevate Software. – MartynA Jan 09 '18 at 12:02
  • Thanks for the assistance, but maybe I should not try and re-invent the wheel and rather maniplulate the code I already have to give me an additional month of data. Is there anyway I can mail you some code that you could look at, I dont want to post all that code in the forum? – Griphon Jan 10 '18 at 07:55