TO_CHAR & TO_DATE

to_char is for converting some value in other data type to a char string

While to_date is used to convert a value to a date type OR to convert a date in one format to another

when we use the combination of both? in what case

v_date date:to_date('12-jun-2003','DD/MON/YYYY');

how can i initialise date while declaring

i know its works even if i just declare like
v_date date:'12-jun-2003';

but i want to get result as 12/jun/2003 i would like to see the action of to_char changing into character format, is what u mean for function of to_char?

can explain me more.

v_date date:=to_date('12-04-1998', 'dd/mon/yyyy');

when i want to see this in dbms_output package i can;t

i am still confuse with to_date and to_char function,

When you specify "v_date date:='12-jun-2003';" you are using implicit conversion based upon your language set-up, but it in my opionion, it is always preferable to use explicit conversion, especially when dealing with dates.

As to your problem of printing the values, in SQL*Plus:

SQL> set serveroutput on;
SQL> DECLARE
  2    l_date DATE := TO_DATE('01/01/2004', 'DD/MM/YYYY');
  3    l_date_formatted VARCHAR2(50);
  4  BEGIN
  5    l_date_formatted := TO_CHAR(l_date, 'Day dth Month');
  6    DBMS_OUTPUT.put_line('formatted = ' || l_date_formatted);
  7  END;
  8  /
formatted = Thursday  4th January

PL/SQL procedure successfully completed.

SQL>

I can;t initialise only one time, do i need to initialise 2 times like u did in declaration and executable part.

like in varchar when i initialise value dbms_output package simply show my value, i can not use to_char or to_date only one time, i can;t do this task by only one declaration and initialisation

to_char and to_date functions will implicitly use the NLS_DATE_FORMAT setting for the current SESSION, if the format string is not given.

Because we developers cannot control in every case what this will be for all users, countries and applications it is usually sensible to provide the date format when we are manipulating and displaying dates.

alter session set nls_date_format='DD/MM/YYYY';
set serveroutput on;
DECLARE
    l_date DATE := '01/01/2004';
BEGIN
     DBMS_OUTPUT.put_line('formatted = ' || l_date );
END;
 /
formatted = 01/01/2004

PL/SQL procedure successfully completed.

alter session set nls_date_format='MM/DD/YYYY';
DECLARE
    l_date DATE := '31/12/2004';
BEGIN
     DBMS_OUTPUT.put_line('formatted = ' || l_date );
END;
 /
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 2

Strictly you don't NEED formatting, it's just that it is BEST PRACTICE.to use it.

Thanx runcsmeduncs you have cleared me, the diff between to_char and to_date!!

No worries.

I remember not being able to distinguish between the two when i first started with oracle.

Goood luck

Duncan

sorry, but your answer isnt right

TO_DATE is for converting strings into DATE-Type and not more
if you deliver a different type oracle is trying to convert them automaticaly. in this case you have database defaults used to format this string.
thats also the reason, why you will get same results as you has seen.
you can deliver a format (like 'mm-dd-yyyy HH24:mi:ss') to define how to interprete the string on first argument.

TO_CHAR has two functions
1. converting numbers into strings
this is usage without a format-parameter

2. converting DATE's into strings
for this you have to deliver a format-string like 'mm-dd-yyyy'

Sorry reading back i have specified to_date twice.

The first statement

TO_DATE: used to convert a number into a varchar2 format

 should have been about to_char

That was my mistake and teaches me to read what i have just written.