Func Toconvert Date Time

create or replace function func_toconvert_date_time (v_date date,type varchar2) return date is -/*---------------------------------------------------------------------------------------------------------*/ --/*--created by : ashish murali -------------------------------------------------------------------*/ --/*--datelastmodified : 05-mar-2008 ---------------------------------------------------------------------*/ --/*--purpose : to change the timestamp into date and time part separately accordin the request -*/ --/* time will be in 01-jan-1900 hh24:mi:ss and date in dd-mon-yyyy 00:00:00 format --*/ --/*--version : 1.0 -----------------------------------------------------------------------------*/ --/*--sample : select func_toconvert_date_time(sysdate,'date') from dual; ----------------------*/ -/*---------------------------------------------------------------------------------------------------------*/ v_output date; --/*-to display the output v_error exception; --/*-handling exception begin -/*---------------------------------------------------------------------------------------------------------*/ --/*----------if the type requested is "date" then output will be date part of the timestamp data ------------*/ --/*----------time part default as 00:00:00 ------------------------------------------------------------------*/ -/*---------------------------------------------------------------------------------------------------------*/ if (type='date') then v_output := to_date(to_char(v_date,'dd-mon-yyyy')) ; return v_output; -/*---------------------------------------------------------------------------------------------------------*/ --/*----------if the type requested is "time" then output will be time part of the timestamp data -----------*/ --/*----------date default as "01-jan-1900"

------------------------------------------------------------------*/ -/*---------------------------------------------------------------------------------------------------------*/ elsif (type = 'time') then v_output := to_date('01-jan-1900 '|| to_char(v_date,'hh24:mi:ss')) ; return v_output; else raise v_error; end if; exception when v_error then raise_application_error(-20001,'please change your type - '||sqlcode||' -error'||sqlerrm); end; /