Anyname Donotcare Anyname Donotcare - 7 months ago 6
SQL Question

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

How to compare parts of date in Informix DBMS:

I wrote the following query but I get a syntax error:

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


I wanna to compare with year and month

How to do this?

Answer

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.