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

Ray wrote:
[color=blue]
> 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[/color]

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.

--

Dave Hughes wrote:
[color=blue]
> Ray wrote:
>[color=green]
> > 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[/color]
>
> 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.[/color]

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.

--

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

Tonkuma wrote:
[color=blue]
> How about this?
> DATE(TRANSLATE('ABCD-EF-GH',DIGITS(MYDATE),'ABCDEFGH'))
> or
> DATE(INSERT(INSERT(DIGITS(MYDATE),5,0,'-'),8,0,'-'))[/color]

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 :-)
[color=blue]
> (Assuming data type of MYDATE is DEC(8,0))[/color]

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.

--

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