Conversion Functions in SQL

Functions in SQL enable we to perform feats such as determining the sum of a column or converting all the characters of a string to uppercase.

SQL provides a number of functions to control date type conversions. These conversion functions convert a value from one datatype to another.

• T0_CHAR(number | date, [‘fmt’]) Converts number or date to character format fmt.

• TO_NUMBER(char) converts char, which contains a number to a NUMBER.

• TO_DATE(‘char’,‘fmt’) converts the char value representing date, into a date value according to fmt specified. If fmt is omitted, format is DD-MON-YY.

• TO_CHAR The TO_CHAR function is freequently used to change a date format from the default to an alternative display format.

TO_CHAR( date, ‘date picture’) #

specifies that date is to be converted to a new format an output.To convert the current date from the default format (DD-MON-YY) to a new ‘date picture’:

SQL> select to_char(sysdate,'day,ddth month yyyy') from dual;

Day and Month in the output are automatically padded with blanksto a length of 9. To remove the blank padding use the FM(Fill Mode) prefix:

SQL> select to_char(sysdate,'fmday, ddth month yyyy') from dual;

FM can be used to suppress leading zeroes for the ddth format, e.g. 05TH is changed to 5th.The case in which the ‘date picture’ is entered is the case in which it will be displayed.TO_CHAR can also be used to extract the time of day only, and display it in a specified format. To display the time of the day:

SQL> select to_char(sysdate,'hh:mi:ss') from dual;

The TO_CHAR function is also used to convert a value of NUMBER data type to a value of CHAR datatype.

TO_CHAR(number,’number picture’) #

SQL> select to_char(sal,'$9,999') from emp;

If the ‘date picture’ is omitted, the date is converted to a char value in ORACLE’s default date format - DD- MON-YY. If the ’number picture’ is not specified, the number is converted to a char value.Also note that format models do not affect the actual internal representation of the column value. They only affect how the column value is displayed when retrieved with a SELECT statement.

The RR Date format Element If you use the RR date format element instead of YY, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. The table summarizes the behaviour of the RR element.

TO_NUMBER #

It is used to transform a number stored as a character to numberdatatype:

SQL> select empno,ename,job,sal from emp where sal>to_number('1500');

TO_DATE #

To show all employees hired on June 4, 1984 (non-default format)we can use the TO_DATE function:

SQL> select empno,ename,hiredate from emp where hiredate=to_date('june
4,1984','month dd, yyyy');

The constant is converted to a date and then compared to the HIREDATE value.

The TO_DATE function is frequently used to supply a value to ORACLE expects to be passed a date with the default date format of DD-MON-YY. If you do not want to use the default date format, you must use the TO_DATE function and the appropriate alterantive format mask.

To enter a row into the EMP table with a non-standard date picture enter:

SQL> insert into emp (empno,deptno,hiredate)
Values(7777,20,to_date(‘19/08/90’,’dd/mm/yy’);
SQL> UPDATE emp SET hiredate = TO_DATE('1998 05 20','YYYY MM DD')WHERE ename =
'smith';