Regarding to the previous question I have asked, i learnt that how to get the last month data in sql, here is my code:

EXTRACT(month FROM CURRENT_DATE)-1 = EXTRACT(month FROM LOG_DTM) and 
EXTRACT(year FROM CU RRENT_DATE) = EXTRACT(year FROM LOG_DTM)

However, if the data type of LOG_DTM is changed into a number, not a time. So now LOG_DTM is the date and LOG_DTM_ID is a number which is based on the LOG_DTM

For example, if the date is 2022-09-30, then LOG_DTM = '2022-09-30' but LOG_DTM_ID is a serious of number 20220930. My code isn't working anymore.

I try to do it add to_number(), but it seems doesn't work. any direciton and ideas? Thank you guys

CodePudding user response：

if the data type of LOG_DTM is changed into a number, ...

... then convert it to a valid DATE datatype value (use to_date, not to_number to do that; of course, with appropriate format model):

extract(month from current_date) - 1 = extract(month from to_date(log_dtm, 'yyyymmdd'))

CodePudding user response：

In order to convert NUMBER to DATE you can use TO_DATE function:

EXTRACT(month FROM CURRENT_DATE)-1 = EXTRACT(month FROM to_date(LOG_DTM,'yyyy-mm-dd')) and 
EXTRACT(year FROM CURRENT_DATE) = EXTRACT(year FROM to_date(LOG_DTM,'yyyy-mm-dd'))