Oracle: tijd naar seconden en omgekeerd omrekenen

Deze functies gebruik ik in een project om de tijd in datum kolommen te vergelijken:


FUNCTION time_to_sec (i IN VARCHAR2) RETURN NUMBER IS
numSecs NUMBER;
BEGIN
numSecs := to_char(to_date(i,'hh24:mi:ss'),'sssss');
RETURN numSecs;
END time_to_sec;

FUNCTION sec_to_time (i IN NUMBER) RETURN VARCHAR2 IS
/*
Round to 0 prevents this exception: ORA-01830: date format picture ends before converting entire input string
*/
numTime VARCHAR2(255);
BEGIN
numTime := to_char(to_date(ROUND(i,0),'sssss'),'hh24:mi:ss');
RETURN numTime;
END sec_to_time;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.