Date Formatting

[tung]

I'm new to PL/SQL, and I love this resource! This is my first post.
Ultimately, I'd like to be able to compare two different dates and pass dates as parameters with the following format: 'MM/DD/YYYY HH:MI:SS'
However, I don't think I understand why the following (designed to familiarize myself with dates) doesn't work:

DECLARE
vardate DATE;
BEGIN
vardate := TO_DATE('9/06/2001 10:36:13','MM/DD/YYYY HH:MI:SS');
dbms_output.put_line (vardate);
END;

Output looks like this: 06-SEP-01
When I would like it to look like this: 9/06/2001 10:36:13

Thanks in advance for your time...

[jmodic]

Your vardate is of type DATE, so you correctly used TO_DATE function to convert a character string into a date format. So when you passed that variable to the DBMS_OUTPUT.PUT_LINE function it realy contains a full date information, including the time part.

But DBMS_OUTPUT.PUT_LINE returns the VARCHAR2 type of result, so it performed an *implicit* date-to-caharacter conversion. As your default date format is set to 'DD-MON-YY', the implicit conversion trimmed the time portion off. If you wan't to disšlay the whole contents of VARDATE variable, use the following:

dbms_output.put_line (TO_CHAR(vardate, 'MM/DD/YYYY HH:MI:SS'));

[Raminder]

What you want to do is convert date into text:

select TO_CHAR(TO_DATE('9/06/2001 10:36:13','MM/DD/YYYY HH:MI:SS'),'MM/DD/YYYY HH:MI:SS') from dual
/

First you convert your string into oracle recognized date and then date into output text of your choice.

[tung]

Jurig, Raminder:
Thank you so much for your prompt reply! It's hard being the "new guy" sometimes, but I take comfort in knowing that I have support from this forum.
Thanks again.
Tung