Re: Date format for 8/4/97, plus TRUNC('ww') question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/05
Message-ID: <33e8911f.27339732@newshost>#1/1

On 5 Aug 1997 02:10:29 GMT, kevin_at_cpm.com (Kevin Bourrillion) wrote:

>1. Why does the date format 'fmMM' work as expected (yielding '8' today),
>but 'fmDD' does not (yielding '04')? Do I really have to do THIS to
>get the date to come out nicely?
>
>TO_CHAR(some_date, 'Dy fmMM/') || LTRIM(TO_CHAR(some_date, 'DD/YY'), '0')
>

No, consider:  

SQL> select to_char( sysdate, 'Dy fmMM/fmDD' ) from dual;  

TO_CHAR(SYSDATE,'DYFMMM/FMDD')

---

Tue 8/05  

SQL> c/fmDD/DD
  1* select to_char( sysdate, 'Dy fmMM/DD' ) from dual SQL> /   TO_CHAR(SYSDATE,'DYFMMM/DD')

---

Tue 8/5

Don't put the second fm in, it's like a quote, it turns it off the second time. From the SQL Lang. Ref:

<quote>
Format Model Modifiers

You can use the FM and FX modifiers in format models for the TO_CHAR function to control blank padding and exact format checking.

A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then re-enabled for the portion following its third, and so on. </quote>

>2. The reference book says TRUNC(some_date, 'ww') should always give a
>Monday. Mine always seems to give a Wednesday. Huh?
>

  1* select to_char( trunc( to_date( '15-MAR-96' ), 'ww' ), 'Day' ) from dual SQL> /   TO_CHAR(TRUNC(TO_DATE('15-MAR-96'),'WW'),'DAY')

---

Monday  

SQL> c/96/97
  1* select to_char( trunc( to_date( '15-MAR-97' ), 'ww' ), 'Day' ) from dual SQL> /   TO_CHAR(TRUNC(TO_DATE('15-MAR-97'),'WW'),'DAY')

---

Wednesday  

ww looks at the year and truncs the date back to the first day that is the same day as the first day of the year..... In 97, jan-1 was wed. In 96, it was monday.

My sql ref manual says:

<quote>

WW        Same day of the week as the first day
          of the year. 

</quote>

Which manual are you using?

>Thanks,
>Kevin

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 05 1997 - 00:00:00 CDT