how to print a date with hours minutes and seconds in a pl sql stored procedure in oracle

[Jhon Parker]

FIRTS QUESTION

how can i print a date with hours minutes and seconds in a pl sql stored procedure in oracle, because i have a column in my table and it's type is DATE, when i do a select for this column I get the entire date with minutes hours and seconds,

but when i try to print that date in a stored procedure using this

DBMS_OUTPUT.PUT_LINE('date value before my if : '|| to_date(my_date, 'DD-MM-YYYY HH24:MI:SS'));

that prints me only the date without the hours minutes and seconds. i'm getting that my_date from a select into like this

select
to_date(ni.my_date, 'DD-MM-YYYY HH24:MI:SS') , ni.id_ni into my_date_iterator, id_iterator
from

i'm doing this print inside a loop but i dont think that that have something to do.


why when i do a normal slect it brings me the hours, minutes and seconds, but when i do a select into inside a stored procedure it dont print me the seconds and minutes and hours, i think that when i do the select into my date variable it brings me the entire date with seconds and minutes but i dont know why it dont print them


SECOND QUESTION

how can I compare two dates that have the same day, month and year.. but they have diferent hours, minutes and seconds

I have this stroe procedure but when i compare the dates it says they are the same:

CREATE OR REPLACE PROCEDURE "comp_date"
IS

minor_date  date;
bigger_date date;


BEGIN
	
	bigger_date := TO_DATE( '11/6/2015 10:11:35', 'dd/mm/yyyy hh24:mi:ss' );
	minor_date  := TO_DATE( '11/6/2015 10:09:35', 'dd/mm/yyyy hh24:mi:ss' );

	
	
	
		if (to_date(bigger_date , 'DD-MM-YYYY HH24:MI:SS') > to_date(minor_date  , 'DD-MM-YYYY HH24:MI:SS')) then
		DBMS_OUTPUT.PUT_LINE('the bigger date is bigger : ');
		
		end if;
		
		if (to_date(bigger_date, 'DD-MM-YYYY HH24:MI:SS') = to_date(minor_date  , 'DD-MM-YYYY HH24:MI:SS')) then
		DBMS_OUTPUT.PUT_LINE('the dates are equals : ');
		
		end if;


END;
/

when i run that stored it says that the dates are equals and it not says that the bigger date is bigger

[Esteban Herrera]

Hi Jhon,

If your column type is DATE, why are you using to_date? This function converts a string to a date.

Why don't you try getting my_date just with:
select ni.my_date, ni.id_ni into my_date_iterator, id_iterator from
And then use the to_char function like this:
DBMS_OUTPUT.PUT_LINE('date value before my if : '|| to_char(my_date, 'DD-MM-YYYY HH24:MI:SS'));

About your second question, I think it's the same problem. bigger_date and minor_date are dates, you don't need to use the to_date function in the if condition, compare them directly:
if (bigger_date > minor_date) then DBMS_OUTPUT.PUT_LINE('the bigger date is bigger : '); end if; if (bigger_date = minor_date) then DBMS_OUTPUT.PUT_LINE('the dates are equals : '); end if;