Sophis Risque: how to convert a relative date into something readable?:)
Sep
19
Written by:
9/19/2013 4:58 PM
When taking a look at some database fields which contain absolute and relative dates, it appears that both formats are stored in a numeric value. In order to know the real value that is stored, some conversion has to be made, using specific limits. In other words, it means that the Sophis Oracle num_to_date method has not be used alone.
The following code permits to display the real value of the date, which can be either absolute or relative:
select (CASE WHEN strike_unit < 731 OR strike_unit > 1826 THEN to_char(strike_unit,'dd/mm/yyyy')
WHEN strike_unit < 1096 THEN concat(to_char(strike_unit-731),'d')
WHEN strike_unit < 1461 THEN concat(to_char(strike_unit-1096),'m')
ELSE concat(to_char(strike_unit-1461),'y')
END) from titres where sicovam=mysicovam