SQL Querying a Database Using The Current Date and Date associated with an Attribute-CodePudding

I am trying to query a from scratch database I created. For each job posting in my database, I store the date of which it was posted in the 'date' datatype, which is in the format of "DD-MON-YY". I want to know which job postings haven't been filled after one month after its posted date.

So far I can query my database and find which jobs haven't been filled, due to a particular attribute being null in my table. But I don't know how to add the condition that it has been at least one month.

SELECT JOBID
FROM JOB
WHERE HIRED IS NULL;

For reference I have created this database in Oracle, and am using SQL developer.

CodePudding user response：

I guess to rephrase, how would I make that condition, would I add one month to the date stored and the compare it with today's date?

Compare your date column to the current date with -1 months added to it:

SELECT JOBID
FROM   JOB
WHERE  HIRED IS NULL
AND    date_column <= ADD_MONTHS(SYSDATE, -1);

CodePudding user response：

Try this

  Select * from table
  Where hired is null and date_col
     >=Add_months
    (posted_date, - 1), 'DD-MON-YY')