How to write dynamically date in sql?-CodePudding

I have a query. It's works fine but my problem is date. I want to calculate dynamically. We are now in august. Instead of changing the date field in the query, I want to give the start and end values of august dynamically.

CodePudding user response：

You can use date arithmetics starting from CURRENT_DATE, which is an expression of the ANSI standard and should be supported by all databases:

SELECT
  CAST(TRUNC(CURRENT_DATE,'MONTH') AS DATE)       first_of_this_month
, CAST(TRUNC(CURRENT_DATE,'MONTH') AS DATE) -1    last_of_last_month
, ADD_MONTHS(CAST(TRUNC(CURRENT_DATE,'MONTH') AS DATE),-1) first_of_last_month
-- out  first_of_this_month | last_of_last_month | first_of_last_month 
-- out --------------------- -------------------- ---------------------
-- out  2022-08-01          | 2022-07-31         | 2022-07-01

CodePudding user response：

You can use DATE_SUB(current_date,INTERVAL DAYOFMONTH(current_date)- 1 DAY) to calculate the first day of current month and LAST_DAY(current_date) to calculate the last day of current month. Your desired query will be as below:

SELECT * COUNT(0) Adet
from Tax t
INNER JOIN Loman l
    ON t.Loman = l.Id AND t.Post IN (13)
WHERE STR_TO_DATE(t.dates, "%Y-%m-%d") 
        BETWEEN DATE_SUB(current_date,INTERVAL DAYOFMONTH(current_date)- 1 DAY) AND
                LAST_DAY(current_date)
  AND Sone NOT IN ('asd', 'as')
GROUP by t.Qua
HAVING COUNT(0) >= 2 
ORDER BY Adet DESC