RE: SQL - Cast Numeric to Date

[rob@xxxxxxxxx]

Expanding upon his suggestion there is nothing to stop you from doing the 
following:
create function DateFromNumber 
  ( NbrDate dec(8,0)) 
  returns date 
  Language SQL 
  returns null on null input 
Begin 
  Declare DateChar char (10); 
  Declare ReturnDate date; 
  Set DateChar=substr(char(NbrDate),1,4) concat '-' concat
               substr(char(NbrDate),5,2) concat '-' concat
               substr(char(NbrDate),7,2); 
  Set ReturnDate = date(DateChar); 
  Return ReturnDate; 
End

Then, whenever you wanted to retrieve a date from your number you could:
select DateFromNumber(20051205)
from sysibm/sysdummy1 

Feel free to update this to put some "Signalling" or something to handle 
invalid dates.


Rob Berendt