CODEWITHSUNDEEP
sqloracle-database
Nidheesh
Nidheesh

Reputation: 4562

Fetch the records from current month

I was trying to pull the TOTAL_PRICE from the cart table using the the following query. Currently it will fetch the TOTAL_PRICE from last 30 days. But I need to modify the query to fetch the TOTAL_PRICE of the current month. ie, PURCHASED_DATE should be in between 1 to 30/31 of the current month.

SELECT TOTAL_PRICE
FROM CUST_CART_TABLE
WHERE USER_ID      = '"+userId+"'
AND (PURCHASED_DATE BETWEEN TO_DATE(SYSDATE-30, 'DD-MM-YY') AND TO_DATE(SYSDATE,'DD-MM-YY')

Upvotes: 0

Views: 95

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Simply compare the month, i.e. 'mm' in combination with 'yyyy':

SELECT TOTAL_PRICE
FROM CUST_CART_TABLE
WHERE USER_ID = '"+userId+"'
AND TO_CHAR(PURCHASED_DATE,'yyyymm') = TO_CHAR(SYSDATE,'yyyymm');

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

SELECT TOTAL_PRICE
FROM CUST_CART_TABLE
WHERE USER_ID='"+userId+"' AND 
TRUNC(PURCHASED_DATE) BETWEEN LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 AND 
                        LAST_DAY(SYSDATE)

Upvotes: 1

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You can use TRUNC and LAST_DAY:

SELECT TOTAL_PRICE
FROM CUST_CART_TABLE
WHERE USER_ID      = '"+userId+"'
AND (TRUNC(PURCHASED_DATE) BETWEEN TRUNC(SYSDATE, 'MM') AND TRUNC(LAST_DAY(SYSDATE)));

Upvotes: 2

Aditya Kakirde
Aditya Kakirde

Reputation: 5215

You can try this one - 

SELECT TOTAL_PRICE
    FROM CUST_CART_TABLE
    WHERE USER_ID      = '"+userId+"'
AND TRUNC(PURCHASED_DATE,'MONTH') = TRUNC(SYSDATE,'MONTH');

It will return records from the current month.

Upvotes: 2

M Akela
M Akela

Reputation: 249

    SELECT TOTAL_PRICE
    FROM CUST_CART_TABLE
    WHERE USER_ID      = '"+userId+"'
    AND (PURCHASED_DATE BETWEEN 
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) 
AND
 SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

Upvotes: 1

Related Questions