How to compare date (month/year) with another date (month/year)

datetime, informix, sql

Solution

If you're going to use the DATETIME route, you need to format the values correctly. As it is, you've written:

select id,name from h_history 
 where ( h_date::DATETIME YEAR TO MONTH >= 2/2012 )
   and ( h_date::DATETIME YEAR TO MONTH <= 1/2013 )

The `2/2012` is an integer division equivalent to 0, and there's no implicit cast from integers to datetime or vice versa.

You could write:

-- Query 1
SELECT id, name
  FROM h_history 
 WHERE (h_date::DATETIME YEAR TO MONTH >= DATETIME(2012-02) YEAR TO MONTH)
   AND (h_date::DATETIME YEAR TO MONTH <= DATETIME(2013-01) YEAR TO MONTH)

That's verbose but precise. You could use a short-cut:

-- Query 2
SELECT id, name
  FROM h_history 
 WHERE (h_date::DATETIME YEAR TO MONTH >= '2012-02')
   AND (h_date::DATETIME YEAR TO MONTH <= '2013-01')

However, since `h_date` is a DATE rather than a DATETIME X TO Y type, there are other options available. The YEAR, MONTH, DAY functions extract the obvious parts from a DATE (and, if you pass a DATETIME to the function, then the DATETIME will be coerced into a DATE and then processed). The only fully locale-independent DATE constructor is the MDY function which takes 3 arguments, the month, day and year. All string representations are subject to interpretation by locale, and therefore won't work everywhere all the time.

You can also do:

-- Query 3
SELECT id, name
  FROM h_history 
 WHERE (h_date >= MDY(2,  1, 2012))
   AND (h_date <= MDY(1, 31, 2013))

Or:

-- Query 4
SELECT id, name
  FROM h_history 
 WHERE ((YEAR(h_date) = 2012 AND MONTH(h_date) >= 2) OR YEAR(h_date) >= 2013)
   AND ((YEAR(h_date) = 2013 AND MONTH(h_date) <= 1) OR YEAR(h_date) <  2013)

Or:

-- Query 5
SELECT id, name
  FROM h_history 
 WHERE (YEAR(h_date) * 100 + MONTH(h_date)) >= 201202
   AND (YEAR(h_date) * 100 + MONTH(h_date)) <= 201301

Given the choice, I'd probably use Query 2 as succinct but accurate, or perhaps Query 5, but all of queries 1-5 are usable.

If `h_date` was a column of some DATETIME type and you need to compare parts of a DATETIME, you can either use casting (as shown in Query 1) or the EXTEND function. That tends to be verbose.

Problem

Original source