problem showing all dates in current month of last year

[elmo_helper]

i have written this code but it only returns those with dates before the current date in the month e.g it onl returns the dates in december before the 8th as that is todays date nd i need it to display all dates in december the year before and it needs to bethe same code for next month.

select customer_id, movie_id,download_date
from tbl_download
where download_date = trunc( add_months( sysdate, -12 ), 'MM' )

i then tried adding


select customer_id, movie_id,download_date
from tbl_download
where download_date = trunc( add_months( sysdate, -12 ), 'MM' ) and download_date <= LAST_DAY(TRUNC(ADD_MONTHS(SYSDATE, -12),'MM'))

and that makes no difference. any ideas how to make this work

[LKBrwn_DBA]

Try:

SELECT customer_id, movie_id, download_date
FROM tbl_download
WHERE TO_CHAR( download_date, 'YYYYMM' )
= TO_CHAR( ADD_MONTHS( SYSDATE, -12 ), 'YYYYMM' );

:rolleyes:

[gandolf989]

You were half right. :rolleyes: You are comparing dates within a given month to midnight on the first of that month and they aren't equal. If there are records that we inserted at exactly midnight of that month then you would get some data, but to get all of the data you need to trunc the download_Date to the beginning of the month.


select customer_id, movie_id,download_date
from tbl_download
where TRUNC(download_date, 'MM' ) = trunc( add_months( sysdate, -12 ), 'MM' );

[gandolf989]

Try:

SELECT customer_id, movie_id, download_date
FROM tbl_download
WHERE TO_CHAR( download_date, 'YYYYMM' )
= TO_CHAR( ADD_MONTHS( SYSDATE, -12 ), 'YYYYMM' );

:rolleyes:

You beat my by less than a minute... ;)

[elmo_helper]

thanks guy both worked a treat, i can explain how grateful i am as i must have stayed up gone 4 last night with this.

[elmo_helper]

i have another problem i have to run a query that finds all the movie titles of the films that have been downloaded by someone when they were 60 when they downloaded it.

in the table tbl_customer there is a column called date_of_birth
there is a download_date column in tbl_download.

im guessing i have to join the two table and somehow code where date_of_birth is >= download_date, - 60

any idea how to do this?

[elmo_helper]

so far i have tried this

select tbl_customer.customer_id, last_name, first_name, date_of_birth, tbl_download.download_date

from tbl_customer RIGHT OUTER JOIN tbl_download
on tbl_download.customer_id = tbl_customer.customer_id

where trunc(date_of_birth, 'MM') = trunc ( add_months (download_date, - 12*60, 'MM')