10 Replies Latest reply on Jun 23, 2008 10:34 AM by 500057

    Converting Date to Day of week

    627574
      I have to convert data from one of my date columns to Day of week numbers.
      i.e., if date is 23-Jun-2008, I have to convert it to 2 as it is Monday.
      I can use 'decode' to convert 'Monday' to 2 and so on.
      But how can I convert 23-Jun-2008 to 'Monday'.
      In my table I am having records created in 2007.

      Thanks & Regards
      Raghu
        • 1. Re: Converting Date to Day of week
          105967
          SQL> select to_char(to_date('23.06.2008', 'dd.mm.yyyy'), 'Day') from dual;

          TO_CHAR(T
          ---------
          Monday

          1 row selected.

          SQL>
          • 2. Re: Converting Date to Day of week
            337410
            I can use 'decode' to convert 'Monday' to 2 and so on.
            hi,

            you don't have to use decode to convert Monday to 2.

            Just change Mannhart's 'DAY' format to 'D'.
            • 3. Re: Converting Date to Day of week
              627574
              Thanks Leo.

              Suppose If I am having the data as day of week can I convert it into the date.
              I mean if the data is in the form of 2,3,4,5...can I convert it into equivalent dates.
              Is it possible as all mondays come under 2 and so on.

              Regards
              Raghu
              • 4. Re: Converting Date to Day of week
                105967
                No, this is not possible.
                Please look in the to_date and to_char(datetime) functions to see what is possible.
                • 5. Re: Converting Date to Day of week
                  561093
                  Not very clear. Can you show what data is residing in your table and what is the expected output.
                  • 6. Re: Converting Date to Day of week
                    627574
                    This is the data I am having now.

                    stop dow day_flg exce_flg
                    -----------------------------
                    370     2     F          F
                    402     6      F          F
                    403     5      F          F
                    404     3      F          F
                    405     7      F          F
                    406     1      F          F
                    408     4      F          F
                    410     2      F          F
                    458     3      F          F
                    459     6      F          F
                    460     7      F          F
                    461     4      F          F
                    462     1      F          F
                    463     5          F          F

                    I want the data to be modified as below:

                    stop dow          day_flg exce_flg
                    ----------------------------------------
                    370     9-Jun-2008     F          F
                    402     13-Jun-2008      F          F
                    403     12-Jun-2008      F          F
                    404     3-Jun-2008      F          F
                    405     14-Jun-2008      F          F
                    406     8-Jun-2008      F          F
                    408     4-Jun-2008      F          F
                    410     2-Jun-2008      F          F
                    458     10-Jun-2008      F          F
                    459     6-Jun-2008      F          F
                    460     7-Jun-2008      F          F
                    461     11-Jun-2008      F          F
                    462     1-Jun-2008      F          F
                    463     5-Jun-2008          F          F

                    Here for the first row , 2 should be modified as 9-Jun-2008, because it is monday.
                    From sunday to saturday the Dow codes are given a 1,2,3,4...7.

                    Hope u understand my problem

                    Message was edited by:
                    Raghunadh
                    • 7. Re: Converting Date to Day of week
                      388131
                      This is the data I am having now.

                      stop dow day_flg exce_flg
                      -----------------------------
                      404     3      F          F
                      458     3      F          F

                      I want the data to be modified as below:

                      stop dow          day_flg exce_flg
                      ----------------------------------------
                      404     3-Jun-2008      F          F
                      458     10-Jun-2008      F          F
                      How could you distinguish the first '3' from the second? Why is it giving different dates?
                      • 8. Re: Converting Date to Day of week
                        627574
                        Not all 3's come under same Tuesday's date.
                        I can have a record for 3rd June and another record for 10th june.
                        For both of them DOW will be 3, dates are different
                        • 9. Re: Converting Date to Day of week
                          Boneist
                          We say to you again, how would you differentiate that one row with 3 is equivalent to 3rd June, and that another row with 3 is equivalent to 10th June?

                          What are the rules for converting from the day of the week to the date?

                          If you don't have any rules, then I don't see how this is possible; Oracle is not psychic (more's the pity!)
                          • 10. Re: Converting Date to Day of week
                            500057
                            It can be achieved but you should know the WEEK number. Based on the WEEK No and the DOW no, you will be able to generate the Dates.

                            Simply based on DOW (No =3) you will not be able to derive the dates.


                            Shailender Mehta