How To Convert Json Date To Dd/mm/yyyy

How to Convert dd/mm/yy format date value to DD-Mon-YY in oracle

$ai Hyderabad Fellow member Posts: 33

Hullo,

In a stored procedure I become an input in the format of dd/mm/yy , I demand to convert into dd-Monday-yy format ?

can you please help me ?

--

sai

Best Respond

• 

  Since you mention a format, I assume the parameter is of varchar2 data type, correct?

  Assuming the parameter is p_1, you tin can:

  TO_CHAR(TO_DATE(p_1,'dd/mm/yy'),'dd-Mon-yy')                      

  Simply you should be handling dates as dates, not every bit strings.

  

Answers

• 

  Since you mention a format, I assume the parameter is of varchar2 data type, correct?

  Assuming the parameter is p_1, you lot can:

  TO_CHAR(TO_DATE(p_1,'dd/mm/yy'),'dd-Mon-yy')                    

  Just y'all should be handling dates as dates, not equally strings.

  

• 

  Hi,

  if your input parameter is a date type   then  y'all can use the TO_CAHR convert function to get it in the dd-Mon-yy format.

  cheers

  

• 

  Sven Westward. Germany Member Posts: 10,562 Gold Crown

  You don't. Just use the datatype DATE. In that location is no format for dates. You but demand to format Date values when you want to convert them into strings to print them on a screen.

  For passing parameters you would always apply Engagement.

• 

  mathguy Member Posts: 10,980 Blackness Diamond

  Hi Sven,   -    I am interested in your comment for my ain learning. Suppose I use sqlplus. I write a query and I want to use demark variables. Every bit far every bit I understand, bind variables in sqlplus can be of number or character data blazon, but they can't be of Appointment type. You said "you would always apply DATE." How would you do that for the scenario I described? I idea the limitation in sqlplus forces me to input the dates equally strings, and process those strings in my SQL queries.

  Give thanks you,    mathguy-ro

• 

  Sven W. Germany Fellow member Posts: 10,562 Gold Crown

  Well $ai specifically mentioned procedures.

  Since you lot are now transfering the problem to Sql*Plus, then commencement we have to distinguish the different languages/tools.

  Sql*Plus is mostly a scripting tool. It has its own gear up of commands.

  And aye in sqlplus we practice not have the same set to datatypes equally it is in sql or plsql.

  So at the moment where you lot switch from sqlplus to SQL|PLSQL you lot should practice the datatype modify.

  eastward.g.

  select * from myTable where inserted_date >= to_date(:MyVariable, 'DD-MON-YYYY');                  

  or for lexical replacements

  select * from myTable where inserted_date >= to_date('&MyVariable', 'DD-Monday-YYYY');                  

  or for plsql

  begin   myPackage.myProc(p_inputDate => to_date(:MyVariable, 'DD-MON-YYYY')); finish; /                  

  In general only at the human interface level we do need to use strings. The database itself should calculate and store DATEs.

• 

  trajon Member Posts: 58 Blue Ribbon

  Hi

  Try this function:

  create part convert_date (v_input varchar2) return varchar2 as

  begin

    render to_char(to_date(v_input,'dd/mm/yy'),'dd-Mon-yy');

  end;

  select convert_date('01/04/16') from dual;

  --------------

  01-Apr-16

• 

  Frank Kulash Boston, United states of america Member, Moderator Posts: 42,883 Cherry Diamond

  Hi,

  mathguy-ro wrote:  Hi Sven,   -    I am interested in your comment for my ain learning. Suppose I employ sqlplus. I write a query and I want to utilise bind variables. Equally far every bit I empathize, bind variables in sqlplus can be of number or character data blazon, but they tin can't be of Appointment type. You said "you would always use DATE." How would you exercise that for the scenario I described? I idea the limitation in sqlplus forces me to input the dates equally strings, and process those strings in my SQL queries.  Thank you,    mathguy-ro                  

  This is really a separate question.  Neither OP nor Sven ever mentioned SQL*Plus or bind variables.

  You should starting time a new thread for this new question.  (You can post a link to that new thread here).

• 

  BluShadow Mr UK Member, Moderator Posts: 42,629 Ruby-red Diamond

  trajon wrote:  Hi  Try this office:  create function convert_date (v_input varchar2) return varchar2 equally brainstorm   render to_char(to_date(v_input,'dd/mm/yy'),'dd-Monday-yy'); end;  select convert_date('01.04.2016') from dual; -------------- 01-Apr-16                    

  Isn't it a bit overkill to write a whole role but to wrap a specific conversion that is washed using to_date and to_char?

  You're lucky if it works likewise, as your functions are using ii digit years, rather than four, so you could end upward getting a wrong result with some dates.

  

• 

  Sven West. Germany Member Posts: 10,562 Gold Crown

  trajon wrote:  Hi  Effort this function:  create function convert_date (v_input varchar2) render varchar2 as begin   return to_char(to_date(v_input,'dd/mm/yy'),'dd-Mon-yy'); end;  select convert_date('01.04.2016') from dual; -------------- 01-April-16                  

  Very bad lawmaking. Very very bad code.

  1) You input a string, you lot output a cord. Why practise any conversion? Just employ the Engagement datatype.

  ii) Lets assume nosotros change the string that yous output dorsum into a engagement.

  Run across what can happen:

  alter session gear up nls_date_format = 'DD-MM-YYYY HH24:MI:SS'; Session altered.  select to_date(convert_date('01/04/1916'),'dd/mm/yy') from dual; TO_DATE(CONVERT_DATE('01/04/1916'),'DD/MM/YY') ---------------------------------------------- 01-04-2016 00:00:00     drib part convert_date;                  

  3) you are wasteing CPU cycles and thereby contributing to climate change just considering of non using the proper datatype.

• 

  mathguy Member Posts: 10,980 Black Diamond

  Understood - it's only by the time I got to Sven's reply I forgot the OP said "in a stored process I get input..." When Sven mentioned "passing parameters" I was already thinking bind variables. I understood the reply - done with this!  Thanks, mathguy-ro

This discussion has been closed.

Source: https://community.oracle.com/tech/developers/discussion/3921496/how-to-convert-dd-mm-yy-format-date-value-to-dd-mon-yy-in-oracle

Share this post