My Creative Arts

Conversion Function in SQL

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

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert the following:

From To

VARCHAR2 OR CHAR NUMBER

VARCHAR2 OR CHAR DATE

NUMBER VARCHAR2

DATE VARCHAR2

The assignment succeed if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

Explicit data type conversion covers in Next post.

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.