1 2 Previous Next 22 Replies Latest reply: Dec 5, 2012 3:05 AM by Kumar Kasinathan Go to original post RSS
      • 15. Re: Problem while converting varchar2 datatype to date
        ascheffer
        And what do you get when you run
        select pac.segment7
        from per_analysis_criteria pac
        where substr( pac.segment7, 5, 1 ) not in ( '/' , '-', '.' )
        • 16. Re: Problem while converting varchar2 datatype to date
          Hoek
          Kumar Kasinathan wrote:
          Thanks Hoek,

          Without the date condition it returns "2013/03/28 00:00:00" and the employee_number is the varchar2 type.
          That value is a valid date after conversion:
          SQL> select * 
            2  from   dual
            3  where TO_DATE('2013/03/28 00:00:00','YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE);
          
          D
          -
          X
          
          1 row selected.
          Anyway, you (still) need to identify the invalid data.
          Anton already provided a query for that.

          Bottom line (again):
          Never ever store dates or numbers as strings (varchar2)
          Never ever rely on implicit datatype conversions

          Calling a column %_number, while it's datatype is varchar2 is a bad idea. It will lead to confusion sooner or later.
          • 17. Re: Problem while converting varchar2 datatype to date
            EdStevens
            For your own sanity, you should learn to format your code. For the sanity of forum members, you should preserve that formatting by bracketing your code with the \
             tags.
            SELECT
            papf.person_id
            , papf.employee_number
            , papf.full_name
            , pac.segment2 work_permit_type
            , pac.segment7 visa_expiry_date
            FROM
            per_all_people_f papf
            , per_person_analyses ppa
            , per_analysis_criteria pac
            , fnd_id_flex_structures_tl fifst
            , fnd_id_flex_structures fifs
            WHERE
            papf.person_id = ppa.person_id AND TRUNC (SYSDATE) BETWEEN
            papf.effective_start_date AND papf.effective_end_date AND
            papf.current_employee_flag = 'Y' AND ppa.analysis_criteria_id =
            pac.analysis_criteria_id AND pac.id_flex_num = fifs.id_flex_num AND
            fifst.id_flex_structure_name ='THE SIT NAME' AND
            fifst.language =USERENV('LANG') AND
            fifst.application_id =fifs.application_id AND
            fifst.id_flex_num =fifs.id_flex_num AND TO_DATE(
            pac.segment7,'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE) AND
            papf.employee_number ='9127327':
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 18. Re: Problem while converting varchar2 datatype to date
              Kumar Kasinathan
              Hoek wrote:
              Kumar Kasinathan wrote:
              Thanks Hoek,

              Without the date condition it returns "2013/03/28 00:00:00" and the employee_number is the varchar2 type.
              That value is a valid date after conversion:
              SQL> select * 
              2  from   dual
              3  where TO_DATE('2013/03/28 00:00:00','YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE);
              
              D
              -
              X
              
              1 row selected.
              Anyway, you (still) need to identify the invalid data.
              Anton already provided a query for that.

              Bottom line (again):
              Never ever store dates or numbers as strings (varchar2)
              Never ever rely on implicit datatype conversions

              Calling a column %_number, while it's datatype is varchar2 is a bad idea. It will lead to confusion sooner or later.
              In the tabel per_analysis_criteria, column segment7 is having different sort of value format like 'E','4.5' etc, but for the particular SIT i.e 'Work Permits' it is having the format '2013/08/18 00:00:00'.

              We are restricting it using join condition in the query ( "AND fifst.id_flex_structure_name='Work Permits").

              It seems the to_date conversion is taking place before the SIT filter condition beeing effected, so it takes all fomat values of column pac.segment7 and try to convert them into date formate and so we are getting the error.

              So i have to concentrate on flow of query execution to restrict it from extracting all segment7 values and then comparing it to filter with particular SIT and i am not sure how to do it.
              • 19. Re: Problem while converting varchar2 datatype to date
                Kumar Kasinathan
                ascheffer wrote:
                And what do you get when you run
                select pac.segment7
                from per_analysis_criteria pac
                where substr( pac.segment7, 5, 1 ) not in ( '/' , '-', '.' )
                Hi ascheffer,

                The segment7 column having different data format strings. But for the pariculuar SIT it holds only DATE format.

                Thanks.
                • 20. Re: Problem while converting varchar2 datatype to date
                  ascheffer
                  select pac.segment7
                  from per_analysis_criteria pac
                  where case when substr( pac.segment7, 5, 1 ) = '/'
                             then to_date( pac.segment7, 'YYYY/MM/DD HH24:MI:SS' ) 
                        end >= trunc( sysdate )
                  • 21. Re: Problem while converting varchar2 datatype to date
                    BluShadow
                    Kumar Kasinathan wrote:
                    The effective_start_date and effective_end_date are date type.

                    Sorry for the wrong information...

                    the pac.segment7 is having '2013/08/18 00:00:00' only for the SIT 'Work Permits' ( "AND fifst.id_flex_structure_name='Work Permits") for other SITs the format differs as 'E', '10' , '23.05' like that.

                    In the tabel per_analysis_criteria, column segment7 is having different sort of value format like 'E','4.5' etc, but for the particular SIT i.e 'Work Permits' it is having the format '2013/08/18 00:00:00'.

                    We are restricting it using join condition in the query ( "AND fifst.id_flex_structure_name='Work Permits").

                    It seems the to_date conversion is taking place before the SIT filter condition beeing effected, so it takes all fomat values of column pac.segment7 and try to convert them into date formate and so we are getting the error.

                    So i have to concentrate on flow of query execution to restrict it from extracting all segment7 values and then comparing it to filter with particular SIT and i am not sure how to do it.
                    So, what you're saying is that you are using a single column for storing different types of data. That is a failure in relational database design. No wonder you're getting problems.

                    Sounds very much like a type of Entity Attribute Value (EAV) model design. It'll haunt you forever...

                    Read the following story...

                    http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
                    • 22. Re: Problem while converting varchar2 datatype to date
                      Kumar Kasinathan
                      Hi every one...

                      Thank you for your support.

                      Some what i have maneged to get the output by including an additional filter condtiotn with the query as beloew.
                      {   AND REGEXP_LIKE (pac.segment7,'[0123456789]{4}/[0123456789]{2}/[0123456789]{2}')
                      }
                      1 2 Previous Next