1

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?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Arvind R.
  • 11
  • 2

1 Answers1

2

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>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks for the feedback. Please mark it answered if it has solved your issue. – Lalit Kumar B Jan 27 '15 at 06:26
  • 1
    Never say *never use YY*. – René Nyffenegger Jan 27 '15 at 06:28
  • 1
    @RenéNyffenegger, and an explanation would be good. I would say, to display, no issues with YY. To convert into date, NO. – Lalit Kumar B Jan 27 '15 at 06:41
  • But this appended date is not updated in the DB when I use the update command. – Arvind R. Jan 27 '15 at 06:54
  • Is it not updated, or is it not being displayed? Post the details, don't say, show. – Lalit Kumar B Jan 27 '15 at 07:00
  • I executed the update command: update SUBS_TARIFF_VARIATION_BACKUP set stv_from_date=STV_FROM_DATE_BCKUP,stv_to_date=STV_TO_DATE_BCKUP where STV_ID=stv_id_bckup ; where the STV_ID is a unique ID.now the value getting posted in the DB is : 01-JAN-00 and not the appended date. – Arvind R. Jan 27 '15 at 07:12
  • `select to_char(stv_from_date, 'DD:MM:YYYY HH24:MI:SS') from SUBS_TARIFF_VARIATION_BACKUP` What is the data type of `stv_from_date`? – Lalit Kumar B Jan 27 '15 at 07:14
  • stv_from_date and stv_to_date are date field. – Arvind R. Jan 27 '15 at 07:15
  • Then it is just a display issue. follow the steps I mentioned in my answer. – Lalit Kumar B Jan 27 '15 at 08:16
  • There is no need whatsoever to do any manipulation to the data in the column. As others have said this is merely a display issue - the data is all there in the column, you just have to tell Oracle which bits of it you want to retrieve. – Boneist Jan 27 '15 at 08:29