5 Replies Latest reply: Sep 28, 2013 11:55 AM by Frank Kulash RSS

    date format mask question

    nick woodward

      just wondering why this first query works, but the second doesn't, if anyone could help please :

       

      [quote]

      SELECT course_no, section_id, to_char(start_date_time, 'Day "The" Ddspth "of" fmMonth yyyy') FROM section

      WHERE to_char(start_date_time, 'DY') = 'SUN';[/quote]

       

      [quote]

      WHERE to_char(start_date_time, 'Day') = 'Sunday'; [/quote]

       

      any ideas? can't see what i'm missing?

       

      thanks in advance,

       

      Nick

        • 1. Re: date format mask question
          EdStevens

          nickwoodward wrote:

           

          just wondering why this first query works, but the second doesn't, if anyone could help please :

           

          [quote]

          SELECT course_no, section_id, to_char(start_date_time, 'Day "The" Ddspth "of" fmMonth yyyy') FROM section

          WHERE to_char(start_date_time, 'DY') = 'SUN';[/quote]

           

          [quote]

          WHERE to_char(start_date_time, 'Day') = 'Sunday'; [/quote]

           

          any ideas? can't see what i'm missing?

           

          thanks in advance,

           

          Nick

           

          can't see what i'm missing

           

          And we can't see what you mean by "doesn't (work)".

          • 2. Re: date format mask question
            nick woodward

            apologies!

             

            it returns no rows, but I can't see why it wouldn't be equivalent to the previous query which returns 4 rows

            • 3. Re: date format mask question
              Solomon Yakobson


              Date format Day returns name of the day blank-padded to length of longest day name (in corresponding language):

               

              SQL> select '[' || to_char(sysdate + 1,'Day') || ']' from dual;

              '['||TO_CHA
              -----------
              [Sunday   ]

              SQL> select '[' || to_char(sysdate + 1,'FMDay') || ']' from dual;

              '['||TO_CHA
              -----------
              [Sunday]

              SQL>

               

              Use FM modifier and ,'nls_date_language=english' to make it NLS independent:

               

              WHERE to_char(start_date_time, 'FMDay','nls_date_language=english') = 'Sunday';

               

              SY.

              • 4. Re: date format mask question
                nick woodward

                perfect! thanks

                 

                yeah fmDay works, as does ='Sunday   '; (which isn't exactly useful )

                 

                i find the whole padding thing with certain formats very strange.... don't really see the use for it with some masks, but not others

                 

                anyway, thanks again!

                • 5. Re: date format mask question
                  Frank Kulash

                  Hi,

                  nickwoodward wrote:

                   

                  ... i find the whole padding thing with certain formats very strange.... don't really see the use for it with some masks, but not others

                   

                  ...

                  My guess is that they figured there was no consensus regarding when padding was more useful for different elements.  Almost everyone consistently  wants 5 seconds padded as '05'; padding isn't wanted quite as often for 5 o'clock, and even less often for the 5th of the month, and (in my experience) padding for days of the week is almost never wanted..  Even if Oracle could determine what most people wanted most of the time, having different padding rules for different elements would confuse everyone.  Once you know about 'fm', it isn't hard to use.