Oracle Date To_Char('Month Dd, Yyyy') Has Extra Spaces in It

Oracle Date TO_CHAR('Month DD, YYYY') has extra spaces in it

if you use 'Month' in to_char it right pads to 9 characters; you have to use the abbreviated 'MON', or to_char then trim and concatenate it to avoid this. See, http://www.techonthenet.com/oracle/functions/to_char.php

select trim(to_char(date_field, 'month')) || ' ' || to_char(date_field,'dd, yyyy')
  from ...

or

select to_char(date_field,'mon dd, yyyy')
  from ...  

Query adding white spaces after a month name

The longest months have length of 9. So, all months are padded with blank spaces to the right to make up 9 characters.

You can specify the fm format model to trim the space.

to_char(to_date('04-May-2013','dd-Mon-yyyy'),'ddth fmMonth yyyy')

How to get month from To_CHAR in oracle?

You can use the fm modifier on month:

select to_char(sysdate,'DD FMMonth YYYY HH24:MI')
from dual

How to format date in DD-MMM-YYYY format eg 29-JAN-2015?

Thanks for answers.
I got the solution. First we need to alter the session as below:

alter session set nls_date_format='DD-MON-YYYY';

then run the query:
SELECT TRIM(TO_DATE('29 Jan 2015'
,'DD MON YYYY'))
FROM DUAL
Now I got result as:29-JAN-2015

how to truncate yyyy/mm/dd hh:mm:ss.SSS to mm/dd/yyyy in oracle

The problem is in the conversion of to_date itself. The below conversion itself is throwing the error you mentioned

select 
TO_DATE('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.SSS') END_DATE
from dual;

You need to use it like below if you want to convert the string with timestamp to timestamp

select TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.FF') from dual

This will simply satisfy your need rather than making so many conversions.

select to_char(
   add_months(
     TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DDHH24:MI:SS.FF'),
   -2),
'mm/dd/yyyy') from dual

Extract month and year from date in oracle

If the field is already a date column, you can simply cast it to the format you want:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'mm-yyyy'),'0') AS A from Doctor_Checkup;

If it is a text column, you will need to cast to a date with format first:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(TO_DATE(CHECKED_DATE,'dd/mm/yyyy'),'mm-yyyy'),'0') AS A from Doctor_Checkup;

How to remove leading zeroes from day and month values in Oracle, when parsing to string using to_char function?

select   to_char(sysdate,'DD.MM.YY') -- Without Fill Mode
,        to_char(sysdate-20,'fmDD.fmMM.YY')  -- With Fill Mode, 20 days ago
  from dual;

Returns

21.03.14    | 1.3.14

FM Fill mode.

In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, which suppresses blank padding, the length of the return value may vary.

---

Related Topics