How to add date condition to my query?

Question

I have this SQL statement. It works, and I need to add another one condition. I need to sort it by date. occurence - is my date row.

SELECT dd.caption, COUNT(t.occurence) 
FROM transaction t 
  INNER JOIN dict_departments dd
    ON dd.id = t.terminal_id
GROUP BY dd.caption

How to add this condition:

WHERE t.occurence BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH)

to my query.

What RDBMS are you on? Many of the relevant functions vary by vendor. — Pieter Geerkens, May 14 '15 at 02:59
possible duplicate of [MySQL - DATE\_ADD month interval](http://stackoverflow.com/questions/6845604/mysql-date-add-month-interval) — shA.t, May 14 '15 at 07:06

score 3 · Answer 1 · answered May 14 '15 at 03:02

3

Try this:

WHERE t.occurrece BETWEEN current_date() AND dateadd(month,1,current_date())

The function dateadd is a SQL SERVER function, but the rest of the clause is standard SQL.

answered May 14 '15 at 03:02

Pieter Geerkens

I understand you, but how can I add it to my existing query? – Шыназ Алиш May 14 '15 at 03:04
Use `DATE_ADD` in MySQL ;). – shA.t May 14 '15 at 07:05

shA.t · Answer 2 · 2015-05-14T08:37:09.033

0

If you want to filter dates from 1 month ago till now:

WHERE (t.occurrece BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 MONTH) AND CURDATE()) = 1

or

WHERE (t.occurrece BETWEEN ADDATE(CURDATE(), INTERVAL -1 MONTH) AND CURDATE()) = 1

edited May 14 '15 at 08:37

answered May 14 '15 at 07:09

shA.t

score 0 · Accepted Answer · answered May 14 '15 at 08:43

BETWEEN requires two arguments, a start point and an end point. If your end point is the current time, you have two options:

WHERE t.occurence BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND NOW()

WHERE t.occurence >= (CURRENT_DATE() - INTERVAL 1 MONTH)

3 Answers3