FROM_TZ converts a timestamp and time zone value into a TIMESTAMP WITH TIMEZONE value. Syntax: FROM_TZ(timestamp, timezone) Example: SELECT FROM_TZ(TIMESTAMP ‘2004-10-24 06:00:00’, ‘2:00’) as RESULT FROM DUAL; RESULT ————– 24-Oct-04 06.00.00 AM +2:00
Length – Oracle SQL Function
LENGTH returns an integer that is the length of the 1st argument. The 1st argument can be a CHAR, VARCHAR2,NCHAR,NVARCHAR2,CLOB or NCLOB datatype. Example: SELECT LENGTH(‘How long is this?’) as RESULT FROM DUAL; RESULT —————- 17
LAST_DAY – Oracle SQL Function
LAST_DAY returns the last day of the month specified in argument1. Argument1 can be a DATE value or a string that can resolve to a DATE datatype. Example: SELECT LAST_DAY(’12-OCT-04′) as RESULT from DUAL; RESULT ————— 31-OCT-04
Lower – Oracle SQL Function
LOWER returns a string that is the same as the 1st argument with all the characters in lowercase. The argument to LOWER can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB. Example: SELECT LOWER(‘Oracle Rocks’) as RESULT FROM DUAL;
LOCALTIMESTAMP – Oracle SQL Function
LOCALTIMESTAMP returns a TIMESTAMP value. You can specify an optional integer argument to specfiy the precision of the second field. Example: SELECT LOCALTIMESTAMP FROM DUAL; LOCALTIMESTAMP —————————- 13-SEP-05 03.02.49.407000 PM
Lpad – Oracle SQL Function
LPAD returns a VARCHAR2 string that is arg1 left padded with arg3 for a length of n. If arg3 is not provided Oracle will pad with blank spaces. Example: SELECT LPAD(’10 + signs’,10,’+’) as RESULT from DUAL; RESULT —————— ++++++++++10
MONTHS_BETWEEN – Oracle SQL Function
MONTHS_BETWEEN returns an integer specfiying the number of months between two dates. The result can be either positive or negative depending on which date is greater. Example: SELECT MONTHS_BETWEEN(10-JAN-2036′, ’10-JAN-2004′ ) AS RESULT FROM DUAL; RESULT ——————- 384
Ltrim – Oracle SQL Function
LTRIM returns a VARCHAR2 string that is the same as arg1 with the characters in arg2 removed. LTRIM keeps scanning from the left most character and keeps removing characters until none match arg2. Example: SELECT LTRIM(‘xxxx-AA’,’x’) as RESULT from dual;
NEW_TIME – Oracle SQL Function
NEW_TIME returns the date and time in timt zone ‘tz2’ based on the time zone ‘tz1’ and the date argument ‘the_date’. Syntax: NEW_TIME(the_date, tz1, tz2) You must set the NLS_DATE_FORMAT parameter to a 24-hour format before you execute the NEW_TIME
Rpad – Oracle SQL Function
RPAD returns a VARCHAR2 string that is arg1 right padded with arg3 for a length of n. If arg3 is not provided Oracle will pad with blank spaces. Example: SELECT RPAD(’10 + spaces’,10,’+’) as RESULT from dual;