Home > SQL, Technical > Conversion functions in Oracle SQL

Conversion functions in Oracle SQL

July 26, 2012

Function 1: TO_NUMBER

Purpose : Returns the given format of data to number

Syntax: TO_NUMBER(‘param1′,’Format’)

Example : SELECT TO_NUMBER(‘$15,789.22′,’$999,999.99’)  FROM DUAL;

Output:  15789.22

———————————————————————————————

Function 2: TO_CHAR

Purpose : Returns the given data into character datatype

Syntax: TO_char(‘param1′,’Format’)

Example : SELECT TO_char(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  2012-07-26

———————————————————————————————

Function 3: TO_DATE

Purpose : Returns the given data into date datatype

Syntax: TO_DATE(‘param1′,’Format’)

Example : SELECT TO_DATE(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  12-JUL-26

———————————————————————————————

Function 3: TO_TIMESTAMP

Purpose : Returns the given data into timestamp datatype

Syntax: TO_TIMESTAMP(‘param1′,’Format’)

Example : SELECT TO_TIMESTAMP(sysdate,’YYYY-MM-DD’)  FROM DUAL;

Output:  12-JUL-26 12.00.00.000000000 AM

———————————————————————————————

Function 4: TO_DSINTERVAL

Purpose : Returns day to second interval

Syntax: TO_DSINTERVAL(‘interval Data’)

Example : SELECT TO_DSINTERVAL(‘1 08:45:45.7846’)  FROM DUAL;

Output:  1 8:45:45.784600000

———————————————————————————————

Function 5: TO_YMINTERVAL

Purpose : Returns Years to months interval

Syntax: TO_YMINTERVAL(‘y-m’)

Example : SELECT TO_YMINTERVAL(’04-05′)  FROM DUAL;

Output:  4-5

Advertisements
Categories: SQL, Technical
%d bloggers like this: