Converting Oracle date format to mm/dd/yyyy using PL/SQL

Data and Databases
1

Hey everyone, I’m working with an Oracle 11g database and I’ve got a bit of a date problem. My dates are currently showing up as ‘01-JUL-85’ but I need them in the ‘mm/dd/yyyy’ format like ‘7/01/1985’.

I’m pretty new to PL/SQL and I’m not sure how to go about this conversion. Can anyone give me a hand with the right syntax or function to use? I’ve tried googling but I’m getting a bit lost in all the different date functions.

Here’s a quick example of what I’m dealing with:

DECLARE
  v_old_date DATE := TO_DATE('01-JUL-85', 'DD-MON-RR');
  v_new_date VARCHAR2(10);
BEGIN
  -- Need help here to convert v_old_date to '7/01/1985' format
  -- and store it in v_new_date
  DBMS_OUTPUT.PUT_LINE(v_new_date);
END;

Any help would be super appreciated. Thanks in advance!

2

I’ve encountered this issue before, and the TO_CHAR function is your best bet here. It’s incredibly versatile for date formatting in Oracle. Here’s how you can modify your code to achieve the desired result:

DECLARE
  v_old_date DATE := TO_DATE('01-JUL-85', 'DD-MON-RR');
  v_new_date VARCHAR2(10);
BEGIN
  v_new_date := TO_CHAR(v_old_date, 'MM/DD/YYYY');
  DBMS_OUTPUT.PUT_LINE(v_new_date);
END;

This will output ‘07/01/1985’. Note that it uses leading zeros for single-digit months and days. If you want to remove the leading zero for the month, you can use the ‘FM’ modifier like this: TO_CHAR(v_old_date, ‘FMMM/DD/YYYY’). This will give you ‘7/01/1985’. The ‘FM’ removes leading or trailing spaces and zeroes.

3

hey finn! have you tried the TO_CHAR function? it’s pretty nifty for date formatting. you could do something like:

v_new_date := TO_CHAR(v_old_date, ‘FMMM/DD/YYYY’);

that should give you ‘7/01/1985’. what do you think? have you run into any other date formatting challenges?

4

yo finn, TO_CHAR is def the way to go. but if u want a lil twist, try this:

v_new_date := TO_CHAR(v_old_date, ‘fmMM/DD/YYYY’);

the ‘fm’ at the start drops leading zeros. so you’ll get ‘7/01/1985’ instead of ‘07/01/1985’. neat huh? lemme know if that works for ya!