-
problem showing all dates in current month of last year
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
-
Try:
Code:
SELECT customer_id, movie_id, download_date
FROM tbl_download
WHERE TO_CHAR( download_date, 'YYYYMM' )
= TO_CHAR( ADD_MONTHS( SYSDATE, -12 ), 'YYYYMM' );
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
You were half right. 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.
Code:
select customer_id, movie_id,download_date
from tbl_download
where TRUNC(download_date, 'MM' ) = trunc( add_months( sysdate, -12 ), 'MM' );
-
Originally Posted by LKBrwn_DBA
Try:
Code:
SELECT customer_id, movie_id, download_date
FROM tbl_download
WHERE TO_CHAR( download_date, 'YYYYMM' )
= TO_CHAR( ADD_MONTHS( SYSDATE, -12 ), 'YYYYMM' );
You beat my by less than a minute...
-
thanks!
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.
-
how to find a date 60 years from another date
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?
-
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')
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|