Cast Date in Informix

Question

I have never used Informix before and I'm trying to write a query that will return records over the last 365 days.

Here is the query I have been trying to use:

Select * from Visit where vis_mod_dt between today-365 and today;

That returns no records even though I know that there is data for the last 365 days. I am guessing that the vis_mod_dt in not a true date column, although it displays as '12/31/1899' I have tried to cast this column using:

select * from visit where date(vis_mod_dt) between today-365 and today;

This still returns no data.

Any ideas?

Answer 1

Informix DATE format

Be aware that the date 1899-12-31 corresponds to the internal date representation of zero (0). That is, internally, Informix stores DATE values in a 4-byte integer, and counts the number of days since 1899-12-31, so Day 1 was 1900-01-01 (and since it knows that 1900 was not a leap year, Day 60 was 1900-03-01).

That fact makes me worry about what is in your table. However, if the data in your table cannot be converted to a DATE upon request, normally you would get an error.

What is your table schema?

It would be sensible for you to establish the schema either using DB-Access and the Info/Tables option, or use DB-Schema:

dbschema -d dbase -t visit

The DB-Schema output is more suitable for adding to your question.

The query expressions using 'TODAY-365' and 'TODAY' should work fine - if there is data to select.

DBDATE environment variable

There is an environment variable, DBDATE, that you may need to set to get things to work - to convert from a string representation to dates. Since you are probably based in the UK (from your icon), then you may want and need to set the value of DBDATE to:

export DBDATE=DMY4/

This says that dates consist of the day, the month, a 4-digit year and the '/' is used as the preferred separator. You won't be surprised to learn that the presumed default value is usually 'MDY4/', for US format; I use 'Y4MD-' habitually, so I see DATE value the same as DATETIME YEAR TO DAY, which is the ISO 8601:2004 notation for a date. (It has many benefits: it is unambiguous, and naive sorting algorithms sort such dates into date order.) There's actually a lot of mechanism in the background in IDS (IBM Informix Dynamic Server - which, I assume, is the DBMS that you are using; there are some alternatives that are also Informix DBMS) such that strings with 2-digit dates will usually be converted correctly (but they are ambiguous and undesirable), and separators other than '/' will be recognized on input, but the slash will be used on 'output' (when converting DATE to string).

---

Information needed to improve the answer to this question - 1^st Edition.

If what is here does not help, then I recommend editing your question to include:

1. The table schema.
2. A few (2-4) rows of data that you think should be selected but aren't.
3. Platform and version information. It can help to have the version down to the level of detail of IDS 11.50.FC4W1; occasionally it matters. Most usually, the first three digits are what affect things, of course.

If your table is big (many columns), try to select the key columns (vis_mod_dt is by far the most important one). Ideally, you won't need any scroll bars in the display. Make sure you don't include any sensitive information.

---

Information needed to improve the answer to this question - 2^nd Edition

I will help you if you pay attention to the questions I ask you. I cannot help you if you do not pay attention to the questions I ask. And please edit your question rather than adding information as an 'answer'.

• What is the table schema? What is the output from:

  SELECT t.tabid, t.tabname, c.colno, c.colname, c.coltype, c.collength
    FROM "informix".systables AS t, "informix".syscolumns AS c
   WHERE t.tabid = c.tabid
     AND t.tabname = "visit"
   ORDER BY t.tabid, c.colno;
  

• What do you get from:

  SELECT TODAY, TODAY-365 FROM "informix".systables WHERE tabid = 1;
  

• Do you have the environment variable DBDATE set? If so, what is its value?

• Do you have the environment variables CLIENT_LOCALE or DB_LOCALE set? If so, what are their values?
• Which version of Informix are you using?
• Which platform are you using it on?
• Which language or tool are you using to run the query.

Note: if you cannot copy'n'paste the queries above, then you probably do not need to include the quoted '"informix".' attributes on the system catalog; however, as written, the queries will work on any extant Informix database - OnLine 5.x, SE 5.x or 7.x, IDS 7.x, XPS 8.x, IDS 9.x or 10.x or 11.x - and any mode of database (unlogged, logged, MODE ANSI). I'd use the JOIN notation except that some of the older versions don't support it - though you have to be on very old versions for that to be a problem.

Answer 2

This is a little confusing, because when I run the following, I get data:

select count(*) from visit where vis_mod_dt between "10/01/2008" and "10/01/2009"

Answer 3

how about unloading the table to ascii file, examine the unloaded vis_mod_dt values to see if they conform to DBDATE=MDY4 (mmddyyyy) format?.. if they do, ALTER vis_mod_dt to TYPE DATE if it's not a DATE column, then LOAD the unloaded table back in.

the: "BETWEEN today-365 AND today" part of your SELECT statement works for me in my apps.