My Creative Arts

Explicit Data Type Conversion

SQL Provides 3 functions to convert a value from one data type to another:

TO_CHAR (number | date, [, fmt] )

This function converts a number or date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER (char, [fmt] )

This function converts a character string containing digits to a number in the format specified by the optional format model fmt.

TO_DATE (char, [fmt] )

This function converts a character string representing a date to a date according to the fmt specified. If fmt is omitted, the format is DD-MON-YY.

Using the TO_CHAR Function with Dates

TO_CHAR(date,'format_model')

The format model:

Must be enclosed in single quotation marks and is case sensitive.
Can include any valid date format element.

Example:

TO_CHAR ( number,'format_model')

Example: here you can notice the both sal and salary printed with formatted result.

Note:

TO_NUMBER()

and TO_DATE()

I'll upload the example if any one ask for me. In the comment section ask for me, and I'll upload it.

Date Function - Working with Dates

SYSDATE is a function that returns the current database server date and time.

Example:

SELECT SYSDATE * DUAL is a dummy table.
FROM DUL;

Result
----------------
30-MAY-13

Arithmetic with Dates

Add or subtract a number to or from a date for a resultant date values.
Subtract two dates to find the number of days between those dates.
Add hours to a date by dividing the number of hours by 24.

Example:

The example on the slide displays the last name and the number of weeks worked by the employee department number 10.

Date Functions

MONTHS_BETWEEN(date1,date2): Finds the number of months between date1 and date2.
ADD_MONTHS(date,n): Adds n number of calender months to date.
NEXT_DAY(date,'char'): Finds the date of the next specified day of the week.
LAST_DAY(date): Finds the date of the last day of the month that contains date.
ROUND(date [, 'fmt']): Returns date rounded to the unit specified by the format model fmt.
TRUNC(date [, 'fmt']): Returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Using Date Functions

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND

TRUNC

Number Functions in SQL

ROUND : Rounds values to specified decimal.

ROUND(45.878,2) -------------------> 45.88

TRUNC : Truncates value to specified decimal.

TRUNC(45.878,2) --------------------> 45.87

MOD : Returns remainder of division.

MOD(1600,300) --------------------> 100

Example:

ROUND

The Round Function rounds the column, expression, or value to n decimal place.

If the second argument is 0 or is missing, the value is round to zero decimal place.

If the second argument is 2, the value is rounded to two decimal places.

If the second argument is -2, the value is rounded to two decimal places to the left.

TRUNC

The TRUNC function truncates the column, expression, or value to n decimal places.

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.

If the second argument is -2, the value is truncated to two decimal places to the left.

Character Functions in SQL

Case-manipulation functions

LOWER :- Converts alpha character values to lowercase.
UPPER :- Converts alpha character values to uppercase.
INITCAP :- Converts alpha character values to uppercase for the first letter of each word.

Character-manipulation functions

CONCAT :- Concatenates the first character values to second character value.
SUBSTR :- Return specified characters from character value.
LENGTH :- Returns the number of character in the expression.
INSTR :- Returns the numeric position of a named string.
LPAD :- Pads the character value right justified to a total width of n.
RPAD :- Pads the character value left justified to a total width of n.
TRIM :- Enables you to trim heading or trailing characters from a character string.
REPLACE :- Searches a text expression for a character string and if found, replaces it with a specified replacement string.

Example:

LOWER