Convert DECIMAL to DATE

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray - 08 Mar 2006 20:34 GMT

I have a table with some audit date and time columns. Problem is the
developer who stored the data left them as DECIMAL type instead of DATE
and TIME. Is there a way I can convert the DECIMAL type to DATE or
TIME? The column data is in the date form YYYYMMDD (i.e. 20060308 =
March 8 2006).

I want to get the data into a DATE type. I tried
TO_DATE('20060308','YYYYMMDD') but I cannot get it to work.

What else can I do to conver the data type?

Thanks,
Ray

Reply to this Message

Dave Hughes - 08 Mar 2006 20:59 GMT

> I have a table with some audit date and time columns. Problem is the
> developer who stored the data left them as DECIMAL type instead of
[quoted text clipped - 9 lines]
> Thanks,
> Ray

Urgh, horrible one, but this seems to work (MYDATE is the field
containing the DECIMAL value):

DATE(INSERT(INSERT(LEFT(CHAR(MYDATE),8),5,0,'-'),8,0,'-'))

There must be a more elegant solution though...

HTH,

Dave.

Reply to this Message

Dave Hughes - 08 Mar 2006 22:45 GMT

> > I have a table with some audit date and time columns. Problem is the
> > developer who stored the data left them as DECIMAL type instead of
[quoted text clipped - 20 lines]
>
> Dave.

Well, I've tried a couple more ways and it does seem that the above is
the simplest way to perform the conversion.

Incidentally, it seems that TO_DATE (aka TIMESTAMP_FORMAT), while
initially appearing to be a really cool function that might be able to
parse all sorts of formats and convert them into TIMESTAMP or DATE
values is in fact the most useless function I've ever come across! When
the documentation states:

Valid format strings are:

'YYYY-MM-DD HH24:MI:SS'

It really means exactly that. In other words, "the *ONLY* valid format
string is". I've tried all sorts of other combinations, but even things
as simple as changing the date-separator, re-ordering the date
components, or removing the time portion from the template fails:

db2 => VALUES TO_DATE('2006/03/08 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

db2 => VALUES TO_DATE('03-08-2006 00:00:00', 'MM-DD-YYYY HH24:MI:SS');
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

db2 => VALUES TO_DATE('2006-03-08', 'YYYY-MM-DD');
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

Which kind of begs the question, why even bother having the second
argument if it can only take a single value?! Bizarre...

Dave.

Reply to this Message

Tonkuma - 09 Mar 2006 11:15 GMT

How about this?
DATE(TRANSLATE('ABCD-EF-GH',DIGITS(MYDATE),'ABCDEFGH'))
or
DATE(INSERT(INSERT(DIGITS(MYDATE),5,0,'-'),8,0,'-'))

(Assuming data type of MYDATE is DEC(8,0))

Reply to this Message

Dave Hughes - 09 Mar 2006 11:48 GMT

> How about this?
> DATE(TRANSLATE('ABCD-EF-GH',DIGITS(MYDATE),'ABCDEFGH'))
> or
> DATE(INSERT(INSERT(DIGITS(MYDATE),5,0,'-'),8,0,'-'))

Ooh, very nice! I don't think I've ever used the TRANSLATE function
before... That's certainly a recipe I'll keep in mind for future use :-)

> (Assuming data type of MYDATE is DEC(8,0))

Yes ... that's why I added the LEFT(..., 8) call in my one, just to
make sure any decimals got stripped off the end, but that could just as
easily be applied to either of your formulae above.

Thanks,

Dave.

Reply to this Message

Ray - 09 Mar 2006 20:23 GMT

Excellent. Thanks a lot to everyone who posted, this was killing me.

Reply to this Message