INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Concatenate Date and Time fields

Concatenate Date and Time fields

(OP)
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

RE: Concatenate Date and Time fields

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

RE: Concatenate Date and Time fields

(OP)
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

RE: Concatenate Date and Time fields

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

RE: Concatenate Date and Time fields

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

RE: Concatenate Date and Time fields

(OP)
Garan,

Thank you so much for the solution.

Have a Happy New Year.

getjbb

RE: Concatenate Date and Time fields

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

RE: Concatenate Date and Time fields

Something like this?

CODE

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

RE: Concatenate Date and Time fields

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close