Dave Hughes wrote:
Ray wrote:
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
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.
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.
--