2

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]

1 Answer 1

5

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

1
  • Thank you so much, it worked!
    – sunShine
    Jan 30, 2023 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.