Convert DECIMAL to DATE
in [DB2]
|
From: Ray on 8 Mar 2006 15:34 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
From: Dave Hughes on 8 Mar 2006 15:59 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. --
From: Dave Hughes on 8 Mar 2006 17:45 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. --
From: Tonkuma on 9 Mar 2006 06:15 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))
From: Dave Hughes on 9 Mar 2006 06:48
Tonkuma wrote: > 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. -- |