CODEWITHSUNDEEP
sqloracle-database
porthfind
porthfind

Reputation: 1619

Oracle: Date format

I'm trying to concat a date and a time which are VARCHAR2 and then pass it to a DATE. The problem is that when I pass it to DATE it does not print the date time ont the dateformat`. What am I doing wrong?

  o_data_final DATE;
  i_data       VARCHAR2:= '12/06/2000';
  i_hora       VARCHAR2:= '12:23:00';

  dbms_output.put_line('print:'||concat(concat(i_data,' '),i_hora));
  --prints correctly: 12/06/2000 12:23:00

  SELECT to_date(concat(concat(i_data,' '),i_hora), 'dd/mm/yyyy hh24:mi:ss')
  INTO o_data_final
  FROM dual;

  dbms_output.put_line('data:'||to_char(o_data_final));
  --prints wrong: 00.06.12

I want that o_data_final be 12/06/2000 12:23:00. Thanks.

Upvotes: 0

Views: 80

Answers (2)

Popeye
Popeye

Reputation: 35930

Then you need to pass the format into TO_CHAR function like:

dbms_output.put_line('data:'||to_char(o_data_final, 'dd/mm/yyyy hh:mi:ss'));

Oracle stores date in internal binary format and it has no specific human-readable format. You will need to use format in TO_CHAR or set the default format for the session using NLS_DATE_FORMAT.

alter session set NLS_DATE_FORMAT = 'dd/mm/yyyy hh:mi:ss';

Currently, you are getting the output: 00.06.12 as currently your NLS_DATE_FORMAT is set to yy.mm.dd

You can fetch the current session value of NLS_DATE_FORMAT using the following query:

SELECT
    *
FROM
    NLS_SESSION_PARAMETERS
WHERE
    PARAMETER = 'NLS_DATE_FORMAT';

Cheers!!

Upvotes: 3

Rustam Pulatov
Rustam Pulatov

Reputation: 665

try please:

  declare 
  o_data_final DATE;
  i_data       VARCHAR2(2000):= '12/06/2000';
  i_hora       VARCHAR2(2000):= '12:23:00';
  begin
  dbms_output.put_line('print:'||concat(concat(i_data,' '),i_hora));
  --prints correctly: 12/06/2000 12:23:00

  SELECT to_date(i_data||' '||i_hora, 'dd/mm/yyyy hh24:mi:ss')
  INTO o_data_final
  FROM dual;

  dbms_output.put_line('data:'||to_char(o_data_final, 'dd/mm/yyyy hh24:mi:ss'));
  end;

or 

  declare 
  o_data_final DATE;
  i_data       VARCHAR2(2000):= '12/06/2000';
  i_hora       VARCHAR2(2000):= '12:23:00';
   begin
  dbms_output.put_line('print:'||concat(concat(i_data,' '),i_hora));
  --prints correctly: 12/06/2000 12:23:00

  SELECT to_date(concat(concat(i_data,' '),i_hora), 'dd/mm/yyyy hh24:mi:ss')
  INTO o_data_final
  FROM dual;

  dbms_output.put_line('data:'||to_char(o_data_final, 'dd/mm/yyyy hh24:mi:ss'));
  end;

Upvotes: 0

Related Questions