How to get record based on current month and year from timestamp column in oracle

Question

How to get record based on current month and year from timestamp column in oracle? I have column in table named payment_time. I want to apply filter on this column based on current month and year. My ...

Accepted Answer

How to get record based on current month and year from timestamp column in oracle? I have column in table named payment_time. I want to apply filter on this column based on current month and year. My payment_time column is in this timestamp format.

20200103161803

Attachment is also attached for

Answer

How to get record based on current month and year from timestamp column in oracle?

Use a filter on your TIMESTAMP column to set the upper and lower bounds. For the lower bound you can TRUNCate the current date to the start of the month and for the upper bound you can do the same and add a month:

SELECT payment_time
FROM   CPDB_TEST_REPORTS.T_X_G2P_EXE_REPORT_N
WHERE  G2PORG_SHORTCODE = '00164752'
AND    payment_time >= TRUNC( SYSDATE, 'MM' )
AND    payment_time <  ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), 1 )

You could also use TRUNC on the payment_time column (however, then Oracle would not use an index on the payment_time column and would need a separate function-based index on TRUNC( payment_time )):

SELECT payment_time
FROM   CPDB_TEST_REPORTS.T_X_G2P_EXE_REPORT_N
WHERE  G2PORG_SHORTCODE = '00164752'
AND    TRUNC( payment_time, 'MM' ) >= TRUNC( SYSDATE, 'MM' )

If your payment_time column is not actually a TIMESTAMP data type but is a VARCHAR2 then you can use exactly the same technique and just convert the filter values to a string of numbers:

SELECT payment_time
FROM   CPDB_TEST_REPORTS.T_X_G2P_EXE_REPORT_N
WHERE  G2PORG_SHORTCODE = '00164752'
AND    payment_time >= TO_CHAR( TRUNC( SYSDATE, 'MM' ), 'YYYYMMDDHH24MISS' )
AND    payment_time <  TO_CHAR( ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), 1 ), 'YYYYMMDDHH24MISS' )

Advertisement

Answer