0

I am using utl_file to write in a csv file. The data includes date columns also. The issue here is

For a single digit day 0 gets removed when the data gets inserted in the csv file. Eg - date 07-Jun-13 gets inserted as 7-Jun-13, how can I ensure that 0 stays?

I already to_char and concat "" but still I get single digit date only. The csv file being generated is used by a 3rd party system hence no change can be made in the csv.

I am using EBS 12.1.3.

Thanks

PL/SQL code snippet

   concat(concat('"',lv_proj_start_date),'"')    ||','||
   concat(concat('"',lv_proj_start_date_plus_1),'"')     ||','||

I expect the output for date in the csv like 07-Jun-13

but in csv file the o/p is 7-Jun-13.

hotfix
  • 3,376
  • 20
  • 36
user3436389
  • 71
  • 2
  • 14
  • Writing CSV files is a bit harder than you might think. I would recommend using a prebuilt solution, such as [this procedure](https://github.com/jonheller1/data_dump/) I maintain. – Jon Heller May 29 '19 at 02:59

1 Answers1

0

Assuming both columns are of DATE format, you need to explicitly convert the date into whichever format you desire, e.g.:

'"' || to_char(lv_proj_start_date, 'dd-Mon-yy', 'nls_date_language=english') || '","' 
    || to_char(lv_proj_start_date_plus_1, 'dd-Mon-yy', 'nls_date_language=english') || '",'

N.B. I have also converted your CONCAT function calls to use the concatenation operator ||, as this is much easier to read. I've also combined text strings where possible.

I have also used the optional third parameter of to_char() to explicitly set the date language to be English, which makes it NLS independent.

A final note: do you absolutely have to output the dates with 2-digit years? 4-digits are preferred nowawdays, post Y2K!

ETA: Here's an example showing that the output is correct:

DECLARE
  lv_proj_start_date DATE := to_date('07/07/2019', 'dd/mm/yyyy');
  lv_proj_start_date_plus_1 DATE := lv_proj_start_date + 1;

  v_str VARCHAR2(4000);
BEGIN
  v_str := '"Testing","' || to_char(lv_proj_start_date, 'dd-Mon-yy', 'nls_date_language=english') || '","' 
                          || to_char(lv_proj_start_date_plus_1, 'dd-Mon-yy', 'nls_date_language=english') || '"';

  dbms_output.put_line(v_str);
END;
/

"Testing","07-Jul-19","08-Jul-19"
Boneist
  • 22,910
  • 1
  • 25
  • 40
  • Thanks Boneist, let me try and get back to you. – user3436389 May 28 '19 at 13:29
  • Hi Boneist, I have another doubt. The user is asking whether the csv file generated fulfills the below properties. CSV Properties: Character Encoding: UTF-8 Quote Char: “ (Double Quote) Separator: , (Comma) Header Line: 1 (Mandatory) Escape Char: Backslash Multi select separator: Semicolon. Can you let me know how can we ensure and check on this? – user3436389 May 28 '19 at 15:03
  • the character encoding will depend on the character set of your database. As for the others, you have the ability to output each line with the required delimiters, etc... – Boneist May 28 '19 at 15:10
  • Tried it and it did not work, in csv it is showing as 7-Jul-13 only. – user3436389 May 28 '19 at 15:28