Concatenate Date and Time fields

[getjbb]

Could someone please help me.

I need to concatenate two fields, date and time.

I tried the following:

DECLARE
v_date DATE := to_date('09-MAR-2015','DD-MON-YYYY');
v_time VARCHAR2(6) := '050520';
v_full DATE;
BEGIN
v_full := TO_DATE(TO_CHAR(v_date, 'DD-MON-YYYY') || ' ' ||TO_CHAR(TO_DATE(v_time,'HH24MISS'),'HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE(v_full);
END;

I received the date, 09-MAR-2015, but not the time.

I would like for the field, v_full, to be: 09-MAR-2015 05:05:20.

Is it possible to get the needed results without defining, v_full, as timestamp.

Thanks in advance,

getjbb

 

[garani]

Hi

You are indeed storing data and time but the display it is showing only DD-MON-YYYY

This is more to do with the setting of the NLS_DATE_FORMAT

I stored your script in a file called x.sql and executed the script as shown below after adjusting NLS_DATE_FORMAT

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'
2 /

Session altered.

SQL> @d:\x
9 /
09-MAR-2015 05:05:20

PL/SQL procedure successfully completed.

Regards
garan

 

[getjbb]

Garan,

Thanks you for the response, but I do not have the privilege to change the date format.

Is there a way of getting around setting NL-DATE-FORMAT?

Perhaps another way to get the needed results.

Thanks,

getjbb

 

[garani]

Hi

as conveyed it's more to deal with the display issue

I think you made a typo it is NLS_DATE_FORMAT not NL_DATE_FORMAT

I don't think you require any special privilege at the session level

SQL> create user test1 identified by test1;

User created.

SQL> grant create session to test1
2 /

Grant succeeded.

SQL> connect test1/test1
Connected.
set serveroutput on

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'
2 /

Session altered.

Elapsed: 00:00:00.12
SQL> @d:\x
9 /
09-MAR-2015 05:05:20

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

garan

 

[garani]

1. Alternatively use TO_CHAR

Change the last line in your code as DBMS_OUTPUT.PUT_LINE(to_char(v_full,'DD-MON-YYYY HH24:MI:SS'));

2. If you are using sqldeveloper you can use preference option to permanently set the format


Tools->Preferences->Database->NLS:

change the Date Format to same as your Timestamp Format.

garan

 

[getjbb]

Garan,

Thank you so much for the solution.

Have a Happy New Year.

getjbb

 

[Beilstwh]

DECLARE
v_date DATE := to_date('09-MAR-2015 050520','DD-MON-YYYY HH24MISS');
BEGIN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_DATE,'MM/DD/YYYY HH24:MI.SS'));
END;

Bill
Lead Application Developer
New York State, USA

 

[ChrisHunt]

Something like this?

DECLARE
   v_date DATE := to_date('09-MAR-2015','DD-MON-YYYY');
   v_time VARCHAR2(6) := '050520';
   v_full DATE;
BEGIN
   -- Produce a string that is v_date concatenated with v_time, and read the result as a date.
   v_full := TO_DATE(TO_CHAR(v_date, 'DD-MON-YYYY') ||' '|| v_time, 'DD-MON-YYYY HH24MISS'); 

   -- Output the resulting date, using a format which exposes the time part
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_full,'DD-MON-YYYY HH24:MI:SS');
END;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Beilstwh]

You can append the time, but why not put it into the single to_date. It is much less typing. Something like

DECLARE
   v_date DATE := to_date('09-MAR-2015 050520','DD-MON-YYYY HH24MISS');
BEGIN
   -- Output the resulting date, using a format which exposes the time part
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date,'DD-MON-YYYY HH24:MI:SS');
END;

Bill
Lead Application Developer
New York State, USA