case is_date and is_string functions in Oracle
Posted on 2008-10-08
I'm trying to come up with a way to check if a field that I'm selecting from a database is a date or a string that I can do within a case statement. here's part of the select:
select /*+ full(i) */
decode(upper(i.TYPE), 'NEW', :NEW_BASE_NAME, :USED_BASE_NAME) BASE_NAME,
substr(o.IA_DEALER_ID, 3) DEALER_ID,
to_char(i.INVENTORY_DATE, 'MM/DD/YYYY') INVENTORY_DATE,
the problem is that i.INVENTORY_DATE is sometimes returning an integer (1, 300) and it is not an actual date. i.VEHICLE.SUB_TRIM_LEVEL is sometimes coming back as a date, rather than a string. I want to put case statements around these two fields, so I can make sure that i.INVENTORY_DATE is coming out as a date, and if not, return NULL, and if i.VEHICLE.SUB_TRIM_LEVEL is coming out as a date and not a string, then return NULL. how can I accomplish this with a case statement? I'm assuming it is not going to be very simple, since there are no is_date or is_string functions in Oracle. thanks!