Convert Decimal value to Date - Informix DB

Question

I am trying to convert decimal value to date in select statement

SELECT user_id, extend(dbinfo("UTC_TO_DATETIME",min(creationdate)), year to fraction) AS earned_date FROM message

But I am getting this error

 Value exceeds limit of INTEGER precision

The field definition in table is this

  creationDate      decimal(14,0) NOT NULL

It seems I am not using the right way to do the conversion, any idea?

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

I don't think you need to use EXTEND in this context - EXTEND is used to change the scale of DATETIME and INTERVAL values.

UTC_TO_DATETIME returns a DATETIME value already.

Have you tried:

SELECT user_id, dbinfo("UTC_TO_DATETIME",min(creationdate)) AS earned_date
  FROM message

What does that produce?

UPDATE

Ahh, an example makes all the difference!

The value you are passing is not a UTC datetime as UTC_TO_DATETIME expects. The function expects an integer representing seconds, not milliseconds. It's as simple as that.

DBINFO('UTC_TO_DATETIME', 1329994172574) exceeds integer precision.

DBINFO('UTC_TO_DATETIME', 1329994172574/1000) on the other hand, produces:

2012-02-23 21:49:32, which I guess is what you expect?

If you have a look at the link I provided earlier, it explains treatment of fractional seconds. (Spoiler: they're ignored.)

If the fractional seconds are critically important, I guess you're going to have to EXTEND the results of this function to fractional seconds, and then add MOD(creationdate,1000)/1000 to it.

For example:

SELECT user_id, 
      (dbinfo("UTC_TO_DATETIME", MIN(creationdate)/1000 )::DATETIME YEAR TO FRACTION(3)
        + (MOD(MIN(creationdate),1000)/1000) UNITS FRACTION) AS earned_date
  FROM message

(But personally, I would be inclined to put this logic into an SPL anyway, so you could call it like: SELECT user_id, millis_to_time(MIN(creationdate))... and avoid writing this sort of complex algorithm all over the place.)

It gives me the same error message I mentioned in the question, as I clarified that the field definition is Decimal(14,0), 1329994172574 is a value example that will cause the error when try to execute the sql. — Ahmad Alkhawaja, Aug 09 '12 at 10:55
See my updated answer. Your example value is what was needed; the fact that the column is defined as DECIMAL(14,0) doesn't tell us what you were putting in it. — RET, Aug 09 '12 at 23:42

score 1 · Answer 2 · answered Aug 08 '12 at 13:45

1

Try like this,

select
    DT,
    DT_Date = convert(datetime,convert(varchar(8),left(dt,8)))
from
    (  -- Test data
    select DT = 19001231.000000
    ) a

Here, DT = 19001231

First 4 Digits (1900) - Year, Next 2 Digits (12) - Month, Last 2 Digits (31) - Date.

It should be similar to the above format then only you can convert. Hope it helps you, Thank You.

answered Aug 08 '12 at 13:45

SuganR

129
1
10

This seems does not run against Informix DB, I am getting syntax error when trying to run it. – Ahmad Alkhawaja Aug 08 '12 at 19:08
No, I think it must be SQL Server - always a risk when you have an 'sql' tag in your question... – RET Aug 08 '12 at 21:25
I thought "sql" tag represents that I need a help in SQL in general, not specific to a database. – Ahmad Alkhawaja Aug 08 '12 at 21:27
2

Yes, that is a quite reasonable assumption. I get the impression there are a lot of SQL Server experts out there that are unaware there are other database technologies... – RET Aug 08 '12 at 21:31

score 1 · Answer 3 · answered Aug 08 '12 at 21:25

I managed to write a stored procedure for this, it seems the only solution? The decimal (14) is in milliseconds, so here is the solution

create procedure "informix".millis_to_time(milli_val decimal(14,0)) returning datetime year to fraction(3);

  define retval datetime year to fraction(3);
  define num_days int;
  define num_seconds int;
  define millis_in_day int;

  let millis_in_day = 86400000;
  let num_days = trunc(milli_val/millis_in_day,0);
  let num_seconds = (milli_val - (num_days * millis_in_day))/1000;

  let retval = extend(mdy(1,1,1970), year to fraction(3));
  let retval = retval + num_days units day;
  let retval = retval + num_seconds units second;

  return retval;
end procedure;

Convert Decimal value to Date - Informix DB

3 Answers3