Regarding date in sql

Question

Here's my query:

select * 
 from test n
WHERE lower(process_name) like 'test%'
  AND (   test_id is NULL 
       OR TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE),-6),'YYYYMM') > TO_CHAR(n.process_date,'YYYYMM')

I want check whether date field process_date is greater than 6 months in the query.

Answer 1

Hi Arav,

If you're using SQL Server, you can use DATEDIFF(). See below example.

DECLARE @a AS DATETIME = '2010-6-1 00:00'; -- Assignment and declaration in SQLS2008
DECLARE @b AS DATETIME = '2010-8-16 00:15';

-- usage: DATEDIFF(Interval, StartDate, EndDate)

SELECT      DATEDIFF(MONTH, @a, @b) AS MonthDifference,
            DATEDIFF(HOUR, @a, @b) AS HourDifference,
            DATEDIFF(MINUTE, @a, @b) AS MinuteDifference;

You can deduct an answer from this. Please let me know if I've missed the target.

Thanks.

Answer 2

I don't have an Oracle instance handy to test if Oracle will do implicit data type conversion on the TO_CHAR results being that it will be entirely numeric. Still, seems over complicated to me when comparing to a TRUNC'd DATE value...

For records that are six months or older in Oracle:

n.process_date <= ADD_MONTHS(TRUNC(SYSDATE), -6)

If you want older but not including six months exactly - remove the equals operator:

n.process_date < ADD_MONTHS(TRUNC(SYSDATE), -6)

For records that are six months or older in MySQL:

n.process_date <= DATE(DATE_SUB(NOW(), INTERVAL 6 MONTH))

If you want older but not including six months exactly - remove the equals operator:

n.process_date < DATE(DATE_SUB(NOW(), INTERVAL 6 MONTH))

For MySQL, DATE is performing similar to Oracle's TRUNC.

Answer 3

SELECT *
  FROM test
 WHERE MONTHS_BETWEEN( SYSDATE, process_date ) > 6

MONTHS_BETWEEN( date1, date2) returns (date1 - date2), so order of args is significant

If you want the months' numbers to differ by 6, for example if this is wrong for you

    MONTHS_BETWEEN( 'JUN 13 2010', 'JAN 16 2010') == 5.9 

Then you should:

SELECT *  
  FROM test
 WHERE MONTHS_BETWEEN( LAST_DAY(SYSDATE), LAST_DAY(process_date) ) >= 6

Last days of months are guaranteed to compare correctly and return whole number

---

NOTE: both those queries will not use index on "process_date", if its available. For 'indexed' solution see post by OMG Ponies and comments to this one