CODEWITHSUNDEEP
oracle-databasedate
ashwinsakthi
ashwinsakthi

Reputation: 1956

Single Month Digit Date Format issue in Oracle

Am getting the below issue when am using 'mon-d-yyyy' to convert date to char, as i need a single day digit for values from 1 to 9 days in a month.

When i use the 'mon-d-yyyy' format, am losing out on 5 days and getting a wrong date. Any help on this would be great.

select to_char(sysdate-22,'mon-d-yyyy') from dual;--aug-2-2017

select to_char(sysdate-22,'mon-dd-yyyy') from dual;--aug-07-2017

select sysdate-22 from dual;--07-AUG-17 11.06.43

Upvotes: 1

Views: 6687

Answers (3)

chikkili j
chikkili j

Reputation: 1

SQL>SELECT TO_CHAR(TO_DATE('29-AUG-2017','DD-MON-YYYY') - 22,'"WEEKDAY :"D, MON-FMDD-YYYY') "Before22Days" FROM DUAL;

D- Gives you a numeric weekday(2nd weekday in a week) on AUG-07-2017.
DD-Gives a Numeric Month Day i.e,07th
FMDD-Gives 7th 

Before22Days
----------------------
WEEKDAY :2, AUG-7-2017

Upvotes: -1

gile
gile

Reputation: 5976

In Oracle date formats, d gets the day of week. The 2 in your output means monday, not august the 2nd.

Try using Fill Mode as Format Model Modifier

select to_char(sysdate-22,'mon-fmdd-yyyy') from dual;

Upvotes: 5

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

One option might be to piece together the date output you want:

SELECT
    TO_CHAR(sysdate-22, 'mon-') ||
    TRIM(LEADING '0' FROM TO_CHAR(sysdate-22, 'dd-')) ||
    TO_CHAR(sysdate-22, 'yyyy')
FROM dual;

The middle term involving TRIM strips off the leading zeroes, if present, from the date.

Output:

enter image description here

Demo here:

Rextester

Upvotes: 0

Related Questions