CODEWITHSUNDEEP
sqloracle11goracle-apex
Vishal Singh
Vishal Singh

Reputation: 25

Date format(0) conversion In Oracle

I am trying to convert the date format stored in my table with a specified format but it is not converting it to the desired format.

I have a table having data in format 15-Jan-2016 00:21:06 CEST and 6/19/2019 3:07:01 AM and I have to convert the date into DD-MON-YY hh24:mi:ss am.

I am using the below query to format the data.

select to_char(to_date(substr(mod,1,20),'DD-MON-YY hh24:mi:ss'),'MM-DD-YYYY HH:MI:SS AM') datetime from test where mod like '%CEST'
union all
select to_char(to_date(mod,'MM-DD-YY hh:mi:ss am'),'MM-DD-YYYY HH:MI:SS AM') datetime from test where mod  not like '%CEST';

It is giving proper output 01-15-2016 12:21:06 AM and 06-19-2019 03:07:01 AM. But I don't want the 0 coming before the month. Is there any specifed date format for removing the 0?

Upvotes: 2

Views: 177

Answers (1)

Littlefoot
Littlefoot

Reputation: 142788

Apply the fm format mask modifier. See res_2:

SQL> select to_char(sysdate, 'mm-dd-yyyy') res_1,
  2         to_char(sysdate, 'fmm-dd-yyyy') res_2
  3  from dual;

RES_1      RES_2
---------- ----------
08-13-2019 8-13-2019

SQL>

Upvotes: 4

Related Questions