Get Data of a month from a dated column-CodePudding

Respected all I want to bring data from a dated column with input of a Month like I have a column in database which type is dated but I want data only mentioning just month and year I tried but got errors please check my query

SELECT 
    M.INV_NUM, M.GD_NUM, M.INV_DATE, M.QTY1,
    D.ITEM_CODE, D.HS_CODE, R.QNTY, R.waste_per, 
    R.WASTE_QNTY, (R.WASTE_QNTY R.QNTY) TOTAL_CONSUMED 
FROM 
    DOCT_EXPT_SALE_MST M,
    DOCT_EXPT_SALE_RAW R,
    DOCT_EXPT_SALE_DTL D
WHERE 
    R.SALE_DET_ID = D.SALE_DET_ID
    AND D.SALE_ID = M.SALE_ID
    AND M.INV_DATE  BETWEEN TO_DATE(TRUNC('072022','MMYYYY')) AND TO_DATE(TRUNC('072022','MMYYYY'))--TO_NUMBER(TO_DATE(TO_CHAR('01072022','DDMMYYYY'))) AND TO_NUMBER(TO_DATE(TO_CHAR('31072022','DDMMYYYY')))
    AND M.COMP_CODE = 3;

I tried many things but all is gone in vain. If anybody help me on this, I shall be very thankful my database is 11g

CodePudding user response：

If you are being passed the string '072022' then you can do:

AND M.INV_DATE >= TO_DATE('072022','MMYYYY')
AND M.INV_DATE < ADD_MONTHS(TO_DATE('072022','MMYYYY'), 1)

The TO_DATE('072022','MMYYYY') clause will give you midnight on the first day of that month, so 2022-07-01 00:00:00.

The ADD_MONTHS(TO_DATE('072022','MMYYYY'), 1) clause will take that date and add one month, giving 2022-08-01 00:00:00.

The two comparisons will then find all dates in your column which are greater than or equal to 2022-07-01 00:00:00, and less that 2022-08-01 00:00:00 - which is all possible dates and times during that month.

So your query would be (switching to ANSI joins!):

SELECT 
    M.INV_NUM, M.GD_NUM, M.INV_DATE, M.QTY1,
    D.ITEM_CODE, D.HS_CODE, R.QNTY, R.waste_per, 
    R.WASTE_QNTY, (R.WASTE_QNTY R.QNTY) TOTAL_CONSUMED 
FROM
    DOCT_EXPT_SALE_MST M
JOIN
    DOCT_EXPT_SALE_DTL D ON D.SALE_ID = M.SALE_ID
JOIN
    DOCT_EXPT_SALE_RAW R ON R.SALE_DET_ID = D.SALE_DET_ID
WHERE 
    M.INV_DATE >= TO_DATE('072022','MMYYYY')
    AND M.INV_DATE < ADD_MONTHS(TO_DATE('072022','MMYYYY'), 1)
    AND M.COMP_CODE = 3;