Date and time columns - days, hours mins between in SQL

[cbsGEM]

I have a start and end date columns (date) and start and end time columns (varchar2).

I need to calculate the days, hours and minute between these 4 columns, I also have to exclude weekends and bank holidays (although I think i can do this bit)

I thought that concatenating the columns and converting might work but it doesnt seem to like it!

I would be very grateful for help.

Thanks,
Gem

[Saii]

Can you please post your query.

Thanks

[cbsGEM]

SELECT act34. mortgage,
act34.applseq,
(TO_DATE(TO_CHAR(PROGOPEN,'DD-MON-YYYY') || ' ' || PROGOTIME ,'DD-MON-YYYY HH24:MI:SS')) OPEN_DT,
decode(NULL,NULL,(TO_DATE(TO_CHAR(PROGCLOSE,'DD-MON-YYYY') || ' ' || PROGCTIME ,'DD-MON-YYYY HH24:MI:SS'))) CLOSE_DT
FROM act34


thats all I have so far

[Saii]

SELECT act34. mortgage,
act34.applseq,
(TO_DATE(TO_CHAR(PROGOPEN,'DD-MON-YYYY') || ' ' || PROGOTIME ,'DD-MON-YYYY HH24:MI:SS')) OPEN_DT,
decode(NULL,NULL,(TO_DATE(TO_CHAR(PROGCLOSE,'DD-MON-YYYY') || ' ' || PROGCTIME ,'DD-MON-YYYY HH24:MI:SS'))) CLOSE_DT
FROM act34


thats all I have so far

select to_date(to_char(dt,'dd-mon-yyyy') ||' '|| to_char(to_date(var,'hh24:mi:ss'),'hh24:mi:ss'),'d d-mon-yyyy hh24:mi:ss') date1 from <table>

This gives the concatenated date from date and varchar fields. maybe u can create a function to wrap this code and pass individual date and varchar field to get date and then use that date values for further processing.

Note: You can do without converting dt to to_char, if needed

Hope that helps!!!