sql developer how to filter date-CodePudding

Peace I have a table full of values and I need to filter all the dates of each month 10 That is to delete everyone who is between 10-01-2022 TO 10-31-2022

And not a specific date as I presented

    create table content(
    cname varchar2(1),
    type varchar2(15),
    episodes number,
    country varchar2(15),
    language varchar2(15),
    releasedate date,
     primary key(cname, type));
    
    insert into content values('A','film', 1, 'China', 'Mandarin', '03/10/2022');
    insert into content values('B','film', 1, 'Taiwan', 'Cantonese', '30/10/2022');
    insert into content values('C','film', 1, 'Singapore', 'Malay', '15/09/2022');
    insert into content values('A','series', 8, 'Korea', 'Korean', '28/09/2022');
    insert into content values('B','series', 10, 'China', 'Mandarin', '03/10/2022');
    insert into content values('C','series', 18, 'Korea', 'Korean', '01/11/2022');
    insert into content values('D','series', 8, 'Korea', 'Korean', '16/09/2022');
    insert into content values('D','documentary', 3, 'China', 'Mandarin', '18/10/2022');
    insert into content values('E','documentary', 6, 'Taiwan', 'Mandarin', '17/10/2022');
    
    
SELECT cname,type,country,episodes,language,releasedate
FROM content
minus
SELECT cname,type,country,episodes,language,releasedate
FROM content
WHERE  content.country ='China'
AND
content.TYPE ='documentary'
AND
content.MONTH(releasedate) =10;

CodePudding user response：

To filter all the dates of each month 10, you can use the MONTH function to extract the month from the releasedate column, and then compare that value to 10. This will return only the rows where the month is 10.

Here is an example query that you can use:

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE MONTH(releasedate) = 10;

Note that this will include all dates within the month of October, including the first and last days of the month. If you only want to include dates between 10-01-2022 and 10-31-2022, you can use the BETWEEN operator to specify the range of dates:

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE releasedate BETWEEN '10-01-2022' AND '10-31-2022';

Keep in mind that the date format may vary depending on your database configuration. You may need to adjust the date format to match the format used in your releasedate column.

CodePudding user response：

If is intended to fetch all data of October without checking the year, we can use EXTRACT, see here the documentation

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE EXTRACT(MONTH FROM releasedate) = 10;

Otherwise, if only the October 2022 should be considered, we will prefer to use BETWEEN rather than two times EXTRACT for both year and month.

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE releasedate BETWEEN 
TO_DATE('2022-10-01','YYYY-MM-DD') AND 
TO_DATE('2022-10-31','YYYY-MM-DD');

If your dates can contain a time, we should add a TRUNC to avoid a date like '10/31/2022 12:22:22' will not be found:

SELECT cname, type, country, episodes, language, releasedate
FROM content
WHERE TRUNC(releasedate) BETWEEN 
TO_DATE('2022-10-01','YYYY-MM-DD') AND 
TO_DATE('2022-10-31','YYYY-MM-DD');

Here we can verify this is working correctly: db<>fiddle