Conversion Functions

Conversion Functions :

• In some cases, the PostgreSQL server uses data of one data type where it expects data of a different data type. When this happens, the PostgreSQL server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the PostgreSQL server or explicitly by the user
• Conversion functions convert a value from one data type to another

• Implicit data type conversions work according to the rules that are explained in the next two slides

Implicit Data Type Conversion :

• The assignment succeeds if the PostgreSQL server can convert the data type of the value used in the assignment to that of the assignment target

• example, the expression hiredate > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date

Explicit Data Type Conversion :

• Explicit data type conversions are done by using the conversion functions

Number Functions

ROUND :

• Rounds value to specified decimal

TRUNC :

• Truncates value to specified decimal

MOD :

• Returns remainder of division

ROUND Function

• ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is –2, the value is rounded to two decimal places to the left (rounded to the nearest unit of 10).
• The ROUND function can also be used with date functions.

Using the ROUND Function :

• SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1);
• 45.92 46 50
  

TRUNC Function :

• TRUNC function truncates the column, expression, or value to n decimal places.
• TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is –2, the value is truncated to two decimal places to the left. If the second argument is –1, the value is truncated to one decimal place to the left.
• Like the ROUND function, the TRUNC function can be used with date functions.

Using the TRUNC Function :

• SELECT TRUNC(45.923,2), TRUNC(45.923,0),TRUNC(45.923,-1);
• 45.92 45 50

Using the MOD Function :

• The MOD function finds the remainder of the first argument divided by the second argument.
  
• For all employees with job title of Sales Representative, calculate the remainder of the salary after it is divided by 5,000.
• SELECT ename, sal, MOD(sal, 5000) FROM emp WHERE job = 'SALESMAN';
• The above example calculates the remainder of the salary after dividing it by 5,000 for all employees whose job ID is SALESMAN