12 Date / Conversion Functions

• ·         Used for text and character data of fixed length, including numbers, dashes, and special characters.-CHAR
• ·         Used to remove padded blanks or to suppress leading zeros-fm
• ·         Functions that convert a value from one datatype to another.-conversion function
• ·         Used to store variable length numeric data.-NUMBER
• ·         Used for character data of variable length, including numbers, special characters, and dashes.-VARCHAR2
• ·         Used for date and time values.-DATE
• ·         Converts dates or numbers to character strings with optional formatting.-TO_CHAR
• ·         Century value depends on the specified year and the last two digits of the current year.-RR
• ·         Converts a character string containing digits to a number with optional formatting.-TO_NUMBER
• ·         Numeric day of the month-DD
• ·         Converts a character string representing a date to a date value with optional formatting.-TO_DATE 

In each of the following exercises, feel free to use labels for the converted column to make the output more readable.

1.    List the last names and birthdays of Global Fast Food Employees. Convert the birth dates to character data in the Month DD, YYYY format

SELECT last_name,TO_CHAR( birthdate, 'Month DD, YYYY') as birthdate

FROM f_staffs;

2.  Convert January 3, 04, to the default date format 03-Jan-2004.

Application Express 5.0.3.00.03:

SELECT  TO_DATE('January 3, 04', 'Month DD YY') as converted

FROM dual;

Application Express 4.0.2.00.09:

SELECT  TO_CHAR( TO_DATE('January 3, 04', 'Month DD YY') , 'DD-Mon-YYYY') as converted

FROM dual;

3.  Format a query from the Global Fast Foods f_promotional_menus table to print out the start_date of promotional code 110 as: The promotion began on the tenth of February 2004.

SELECT 'The promotion began on the ' || TO_CHAR(start_date, 'ddthsp "of" Month YYYY') as output

FROM f_promotional_menus

WHERE code = 110;

4.  Convert today’s date to a format such as: “Today is the Twentieth of March, Two Thousand Four”

SELECT 'Today is the ' || TO_CHAR(SYSDATE, 'fmDdthsp "of" Month, Year') as today

FROM dual;

Today is the Ninth of October , Twenty Sixteen

Today is the Ninth of October, Twenty Sixteen

5.  List the ID, name, and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places.

SELECT id, first_name ||' '||last_name as  name, TO_CHAR( salary, '$999999.99') as salary

FROM f_staffs;

[salary column has datatype NUMBER(8,2)]

6.  Ellen Abel is an employee who has received a $2,000 raise. Display her first name and last name, her current salary, and her new salary. Display both salaries with a $ and two decimal places. Label her new salary column AS New Salary.

SELECT first_name, last_name, TO_CHAR( salary, '$999999.99') as "Old Salary", TO_CHAR( salary + 2000 , '$9999999.99') as "New Salary"

FROM employees

WHERE first_name = 'Ellen'  AND last_name = 'Abel' ;

Please note extra digit here. Column Salary datatype is NUMBER(8,2). Suppose his current salary is $999999.99, even if I add 0.01 and keep precision same, I will get:

SELECT first_name, last_name, TO_CHAR( salary, '$999999.99') as "Old Salary", TO_CHAR( salary + 0.01 , '$999999.99') as "New Salary"

FROM employees

WHERE first_name = 'Ellen'  AND last_name = 'Abel' ;

  

7.  On what day of the week and date did Global Fast Foods’ promotional code 110 Valentine’s Special begin?

SELECT TO_CHAR(start_date, 'fmdd-Mon-YYYY (Day)') as startdate

FROM f_promotional_menus

WHERE  code = 110;

10-Feb-2004 (Tuesday ) – has extra space after day of week.

10-Feb-2004 (Tuesday) – no extra space after day of week

8.  Create one query that will convert 25-Dec-2004 into each of the following (you will have to convert 25-Dec-2004 to a date and then to character data):

December 25th, 2004

SELECT TO_CHAR(TO_DATE('25-Dec-2004', 'dd-Mon-yyyy'), 'Month ddth, yyyy') as changeddate

FROM dual;

DECEMBER 25TH, 2004

SELECT TO_CHAR(TO_DATE('25-Dec-2004', 'dd-Mon-yyyy'), 'MONTH DDth, yyyy') as changeddate

FROM dual;

25th december, 2004

SELECT TO_CHAR(TO_DATE('25-Dec-2004', 'dd-Mon-yyyy'), 'fmddth month, yyyy') as changeddate

FROM dual;

9.  Create a query that will format the DJs on Demand d_packages columns, low-range and high-range package costs, in the format $2500.00.

SELECT code, TO_CHAR(low_range,'$999999.99') as low_range, TO_CHAR(high_range,'$999999.99') as high_range

FROM d_packages ;

Data type is NUMBER(6,0), so after dot, I won’t  eventually get anything else than 00 J

10. Convert JUNE192004 to a date using the fx format model.

SELECT TO_DATE('JUNE192004','fxfmMONTHddyyyy') as changeddate

FROM dual; 

Application Express 5.0.3.00.03:  19-Jun-2004

Application Express 4.0.2.00.09:  06/19/2004

Why extra fm, just try: SELECT TO_CHAR(  TO_DATE('JUNE192004','fxMONTHddyyyy'), 'fxMONTHddyyyy')

FROM dual;

11. What is the distinction between implicit and explicit datatype conversion? Give an example

of each.

1). Implicit conversion:

SELECT SYSDATE

FROM dual;

Application Express 5.0.3.00.03:  19-Jun-2004

A date being converted to character data type using default format of 'DD-Mon-YYYY'

But in Application Express 4.0.2.00.09:  06/19/2004

That’s why I might not like implicit conversion and switch to explicit:

2). Explicit conversion:

SELECT TO_CHAR(SYSDATE, 'MONTH DDth, yyyy')

FROm dual;

A date being converted to character data type using non-default format 'MONTH DDth, yyyy'

12. Why is it important from a business perspective to have datatype conversions?

This is required for database to meet the basic need of application - being of use to the user. Date is saved as number internally in db which no user may be able to understand. $ sign printed in front of a number gives idea which currency application is talking about. It could have been euro dollar INR or anything a simple number that’s it. The value of data in db lies in the fact that it conveys useful information and can be processed after conversions.