Andrew Andrew - 3 months ago 8
SQL Question

extract the date from ID and get the data for last one year

I have below query in which i can extract the date using

to_date(substr(id, 1, 14), 'YYYYMMDDHH24MISS')
from id column. But now i want to get the data for last one year comparins this date from id column.

select count(*) as cnt from in_test where id > 201200000000000000 and status not in (0,1)

Answer

If you want to get the data that is equal or later than today, one year ago, then:

where  id >= to_number(to_char(add_months(sysdate,-12), 'YYYYMMDD'))*10000000000

This assumes that you have given the correct number of digits in your question. Otherwise you may need to append/remove some zeroes from that final number.

For today, the expression on the right results in this number:

201508310000000000

Explanation

sysdate is the Oracle way to get the current time stamp as a datetime.

add_months(...,-1) subtracts 1 year from that date, so if we do this today (31 August 2016), we get 201510831.

to_char(..., 'YYYYMMDD') converts that datetime to a string, in the format YYYYMMDD. So that will result in the string 20150831.

to_number(...) converts that string to numeric data type, i.e. to 20150831.

*10000000000 multiplies that number so that it gets the same range like your id range seems to be, which gives the final result 201508310000000000.