8 Replies Latest reply: Dec 11, 2012 7:39 AM by EdStevens RSS

    format

    Costa
      Hi,
      I want to find those id where (leaving- joining) <> duration.

      eid joining leaving DURATION
      1001 31-JAN-2012 12-MAR-2012 2YR
      1002 12-FEB-2012 12-FEB-2012 1 YEAR
      1003 12-FEB-2012 12-MAR-2012 1MONTH




      Problem is that in variable duration, data are avaiilable with different format i.e. YEAR, YEARS, YRS, MONTH, MONTHS, DAY, DAYS, HOUR, HOURS, HR, MINUTES, MINUTE, SECONDS, SECOND, SEC .

      How to resolve. Can anyone help me.
        • 1. Re: format
          NSK2KSN
          what is your expected output
          • 2. Re: format
            vijayrsehgal-Oracle
            Costa,

            your question is not clear, could you put more details as to what you really want, also if you can share the table structure (ddl) and sample data (insert statement) and required output.
            • 3. Re: format
              hm
              I guess you have a problem with the "free text" in column DURATION.
              There will be no easy solution. Better you use a standard format for that column.
              • 4. Re: format
                Costa
                In the db, employee joining date and leaving date are entered manually, then calculate the "duration" by subtracting joining date from leaving date.
                The duration is entered manually from the front end not in proper format.

                if emp joining date='23-JAN-2012' and leaving date ='25-JAN-2012' then duration must be 2 days
                But in front end this days have been entered like this 2 days, 2day, 2 days.

                How can I convert those type of data into numeric. (same for month, year,hour, minute, seconds)
                • 5. Re: format
                  jeneesh
                  DECODE

                  Or

                  CASE
                  • 6. Re: format
                    mschnatt
                    The duration is a derived attribute from leaving - joining.

                    If you make

                    SELECT leaving-joining
                    FROM ....


                    you get the amount of days of the duration - a number.
                    • 7. Re: format
                      vijayrsehgal-Oracle
                      since joinging_date and leaving_date are both dates you will always get number of days if you substract leaving_date - joining_date.
                      • 8. Re: format
                        EdStevens
                        Costa wrote:
                        In the db, employee joining date and leaving date are entered manually, then calculate the "duration" by subtracting joining date from leaving date.
                        The duration is entered manually from the front end not in proper format.

                        if emp joining date='23-JAN-2012' and leaving date ='25-JAN-2012' then duration must be 2 days
                        But in front end this days have been entered like this 2 days, 2day, 2 days.
                        An astoundingly bad design. Why store 'duration' at all? ESPECIALLY as free form text? Duration can always be calculated from joining_date and leaving_date. One of the fundamental principles of data design is never store a value that can be calculated from other stored values.
                        How can I convert those type of data into numeric. (same for month, year,hour, minute, seconds)