How do I get the date next week in SQL (Firebird)?

Question

I want to get a date using an SQL statement. For instance, if the date is '2018-08-07' output this should return: '2018-08-14' (one week later). Then I'm going to get the total rows between these dates.

SELECT count(*) FROM orders  WHERE dates BETWEEN '2018-08-07'  AND 
DATEADD(day,7,'2018-08-07');

However, i get this error because of dateadd() function:

SQL Error [335544634] [42000]: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 1; DATEADD [SQLState:42000, ISC error code:335544634]

Answer 1

You need to use DATEADD(day,7,date '2018-08-07') , note the keyword date used.

Consider the following data

create table orders(
  dates date
);

INSERT INTO orders (dates)
SELECT  '2018-08-05 ' FROM RDB$DATABASE UNION ALL
SELECT  '2018-08-06' FROM RDB$DATABASE UNION ALL
SELECT  '2018-08-07' FROM RDB$DATABASE UNION ALL
SELECT  '2018-08-08' FROM RDB$DATABASE UNION ALL  
SELECT  '2018-08-09' FROM RDB$DATABASE;

SELECT count(*) 
FROM orders 
WHERE dates BETWEEN '2018-08-07'  AND  DATEADD(day,7,date '2018-08-07');

Result

COUNT
  3

https://dbfiddle.uk/7Vh6wDiw