Date Format

[ryand09]

I'm trying to output a date including the time. I using the format below but I'm only getting the day month and year?

Thanks in advance.

declare
DateReq varchar2(15);
TimeReq varchar2(15);
x date;

begin
DateRequired:='1-feb-2005';
TimeRequired:='08:30:30';
x:=to_date(DateReq || ' ' || TimeReq, 'dd-mon-yyyy hh24:mi:ss');
DBMS_OUTPUT.put_line('x is '|| x);
end;

--Output
--x is 01-FEB-05

[RGM]

date format is set by oracle either set the defaults to show time or override with the following code.

declare
DateReq varchar2(15);
TimeReq varchar2(15);
x date;
begin
DateReq:='1-feb-2005';
TimeReq:='08:30:30';
x:=to_date(DateReq || ' ' || TimeReq, 'dd-mon-yyyy'|| ' '|| 'hh24:mi:ss');
DBMS_OUTPUT.put_line('x is '|| to_char(x,'dd-mon-yyyy hh24:mi:ss'));
end;

[swatson]

Hi there-
The previous responder was correct to point out that you were converting a character string to a date data type then displaying the date with default formatting, a second operation.

However, it actually is possible to override default formatting without using an explicit to_char format command each time; You can use the ALTER SESSION command to reset the default NLS_DATE_FORMAT for that session... for example:

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
16-DEC-05

SQL> ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY hh24:mi:ss"
2
SQL> /

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
12/16/2005 12:24:01

Ciao!