Oracle Magazine - January/February 2012

You can also use EX TRAC T to extract and return the value of a specified element of a date. For example

1. What year is it?

EXTRACT (YEAR FROM SYSDATE)

2. What is the day for today’s date?

EXTRACT (DAY FROM SYSDATE)

To convert a string to a date, use the TO_ DATE or the TO_ TIMESTAMP built-in function. Provide the string and Oracle Database returns a date or a time stamp, using the default format mask for the session:

DECLARE l_date DATE;

BEGIN l_date := TO_DATE ('12-JAN-2011');

END ;

If the string you provide does not match
the default format, Oracle Database will
raise an exception:

DECLARE l_date DATE;

BEGIN l_date := TO_DATE ('January 12 2011');

END;

/ ORA-01858: a non-numeric character was found where a numeric was expected

You should not assume that the literal value you provide in your call to TO_DATE

Take the Challenge!

Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge ( plsqlchallenge.com). You can read and take the quiz here in Oracle Magazine and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win an e-book from O’Reilly Media ( oreilly.com).

Question 1

Oracle Database provides a function for returning the date of the last day of the month. It does not, however, provide a function for returning the date of the first day. Which of the following can be used to do this?

a.

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)

RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in);

END;

/

b.

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)

RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in, 'MM');

END;

/

c.

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)

RETURN DATE
IS
BEGIN
RETURN TRUNC (date_in, 'MONTH');
END;

/

d.

CREATE OR REPLACE FUNCTION plch_first_day (date_in IN DATE)

RETURN DATE
IS
BEGIN
RETURN TO_DATE (TO_CHAR (date_in, 'YYYY-MM')
|| '-01', 'YYYY-MM-DD');

END;

/

Question 2

Given this declaration section:

DECLARE
c_format CONSTANT VARCHAR2 ( 22)
:= 'YYYY-MM-DD HH24:MI:SS' ;
l_new_year DATE
:= TO_DATE (

'2012-01-02 00:00:01' , c_format);

which of the following blocks offers an exception section so that after that block is executed, the date and time 2012-01-01 00:00:01 will be displayed on the screen?