CODEWITHSUNDEEP
sqloracle-databaseto-dateto-char
Arvind R.
Arvind R.

Reputation: 11

Appending the HH:MI:SS to to_date function

I have a table in DB from which I take values from a date field.I store this date value in a cursor. currently the date value is of the format DD:MON:YY. Now I convert this date into character using to_char function so as to append time 00:00:00 to it. now I tried converting back to the date format , but the timestamp is not appended and the date format is not as I have given( format is same as that of the date field in DB).but the to_char function returns the correct format as I have given.

Some of the code snippets are as follows:

Initialized a cursor as 

cursor cur is
select to_char(STV_FROM_DATE,'DD:MON:YYYY:')STV_FROM_DATE :---from a table in DB
cur1 cur%rowtype;
begin
open cur;
loop
fetch cur into cur1;
dbms_output.put_line(cur_1.STV_FROM_DATE);

This is giving the value correctly as:

01:JAN:2000:
01:JAN:2000:
01:JAN:2000:
01:JAN:2000:

Now I appended the timestamp 00:00:00 to this and did the to_date operation as follows:

STV_FROM_DATE_BC := cur_1.STV_FROM_DATE;
STV_FROM_DATEBCKUP:=to_date(STV_FROM_DATE_BC,'DD:MM:YY:HH24:MI:SS');
dbms_output.put_line(STV_FROM_DATEBCKUP);

The result obtained is:

01-JAN-00
01-JAN-00
01-JAN-00

Could anyone help me to solve this issue and convert the timestamp appended character to date?

Upvotes: 1

Views: 604

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

DBMS_OUTPUT is to DISPLAY. so, to display a DATE, you need TO_CHAR.

STV_FROM_DATEBCKUP:=to_date(STV_FROM_DATE_BC,'DD:MM:YY:HH24:MI:SS'); dbms_output.put_line(STV_FROM_DATEBCKUP);

Never use YY, always mention 4 digits YYYY for the complete year. You don't want to introduce the Y2K bug again. Above, you are trying to display the date value, but you did not mention the format model that you want to display. So simply use TO_CHAR along with proper format model.

Reason Without providing a proper format model, your client would just display according to your locale-specific NLS settings. For example, if I just display the sysdate, I would see the format that is mentioned in my NLS_DATE_FORMAT in v$parameters.

SQL> select parameter, value from v$nls_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER            VALUE
-------------------- --------------------
NLS_DATE_FORMAT      DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
---------
27-JAN-15

SQL>

So, I got 27-JAN-15 as SYSDATE, since my NLS_DATE_FORMAT is DD-MON-RR. You could set it at system level or at session level.

SQL> alter session set NLS_DATE_FORMAT='DD:MM:YYYY:HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
27:01:2015:11:54:07

SQL>

So, you could either set your NLS_DATE_FORMAT to set the format model that suits you. Let's see a test case -

SQL> set serveroutput on
SQL> DECLARE
  2    STV_FROM_DATE_BC   VARCHAR2(20);
  3    STV_FROM_DATEBCKUP DATE;
  4  BEGIN
  5    STV_FROM_DATEBCKUP:= NULL;
  6    STV_FROM_DATE_BC  :='01:JAN:2000:';
  7    STV_FROM_DATE_BC  :=STV_FROM_DATE_BC||'00:00:00';
  8    dbms_output.put_line('Input date literal = '||STV_FROM_DATE_BC);
  9    STV_FROM_DATEBCKUP:=to_date(STV_FROM_DATE_BC,'DD:MON:YYYY:HH24:MI:SS');
 10    dbms_output.put_line('Date without format model = '||STV_FROM_DATEBCKUP);
 11    dbms_output.put_line('Date with proper format model = '||TO_CHAR(STV_FROM_DATEBCKUP,'DD:MM:YYYY:HH24:MI:SS'));
 12  END;
 13  /
Input date literal = 01:JAN:2000:00:00:00
Date without format model = 01-JAN-00
Date with proper format model = 01:01:2000:00:00:00

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions