CODEWITHSUNDEEP
dateinformix
Francisco Garcia
Francisco Garcia

Reputation: 35

Issue with dates in Informix DB 11.70

I'm using informix DB 11.70, and this issue is ticking me off pretty badly.
When I insert a client into my DB, no matter what date I input, when I fetch back the date it brings some random date in 1894.
What issue could it be? It throws no errors when I send entries, no 'Invalid month in date' nonsense.
Pls help.

Upvotes: 1

Views: 145

Answers (1)

RET
RET

Reputation: 9188

The "epoch date" for Informix is 01-01-1900. In other words, date ZERO = "12-31-1899", date 1 = "01-01-1900", date 2 = "01-02-1900", today ("09-24-2013") is stored numerically as 41540.

I think either your client application is being too clever by half, or you aren't putting quotes around your dates, and either way the server is treating your dates as numeric calculations.

In other words, you enter something like 05-11-2010, and your client sends that to the database as 5 minus 11 minus 2010, or -2016. Day number -2016 is "06/24/1894".

I bet if you enter your dates with a slash rather than a dash (i.e. "09/24/2013"), you'll always get 9 divided by 24 divided by 2013 = close to zero => "12/31/1899".

You haven't said if you're using plain old SQL via DB-Access, or ODBC, or something else. But I'll guarantee your dates are not being enclosed in quotes, and are therefore getting treated as arithmetic expressions.

Upvotes: 3

Related Questions