CODEWITHSUNDEEP
sqldatedecimalinformix
Ahmad Alkhawaja
Ahmad Alkhawaja

Reputation: 539

Convert Decimal value to Date - Informix DB

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?

Upvotes: 3

Views: 4988

Answers (3)

RET
RET

Reputation: 9188

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.)

Upvotes: 3

Ahmad Alkhawaja
Ahmad Alkhawaja

Reputation: 539

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;

Upvotes: 1

SuganR
SuganR

Reputation: 129

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.

Upvotes: 1

Related Questions