1 2 Previous Next 20 Replies Latest reply: Mar 4, 2013 10:33 AM by 779320 RSS

    Date Conversion issue

    779320
      Dear All,

      I'm working on Oracle 11g.

      I have a column of varchar2 type but date is passed to it. the column name is proj_date coming from a table
      it is showing data as 2014/06/10 00:00:00

      I have one more other column in another table which is defined as
      TO_CHAR (TO_CHAR (SYSDATE, 'YYYY') + 1) NEXT_YEAR.
      This is stored in the table as varchar2

      Now i need to compare these two dates in a sql

      select
      case when PROJ_DATE between ('01-JAN-'||NEXT_YEAR) AND ('31-MAR-'||NEXT_YEAR) THEN
      100
      else
      200
      end
      from tab_a a,tab_b b
      where a.emp_num=b.employee_number

      But this is throwing me an error saying
      ORA-00932 Inconsistent data types: expected char got number


      Any help on this pls.

      Thx
        • 1. Re: Date Conversion issue
          sb92075
          776317 wrote:
          Dear All,

          I'm working on Oracle 11g.

          I have a column of varchar2 type but date is passed to it. the column name is proj_date coming from a table
          it is showing data as 2014/06/10 00:00:00

          I have one more other column in another table which is defined as
          TO_CHAR (TO_CHAR (SYSDATE, 'YYYY') + 1) NEXT_YEAR.
          This is stored in the table as varchar2

          Now i need to compare these two dates in a sql

          select
          case when PROJ_DATE between ('01-JAN-'||NEXT_YEAR) AND ('31-MAR-'||NEXT_YEAR) THEN
          100
          else
          200
          end
          from tab_a a,tab_b b
          where a.emp_num=b.employee_number

          But this is throwing me an error saying
          ORA-00932 Inconsistent data types: expected char got number


          Any help on this pls.

          Thx
          flawed & unprofessional table "design"!

          The purpose of TO_DATE is to convert string datatype to DATE datatype.
          With Oracle characters between single quote marks are STRINGS!
          'This is a string, 2009-12-31, not a date'
          When a DATE datatype is desired, then use TO_DATE() function including format.

          case when PROJ_DATE between ('01-JAN-'||NEXT_YEAR) AND ('31-MAR-'||NEXT_YEAR) THEN
          do NOT compare strings; only compare DATE datatypes
          • 2. Re: Date Conversion issue
            779320
            Thx for the reply,
            Yes understand that but each and every column in that table would be of varchar2 type which cannot be helped as it is a seeded table.
            I can change the NEXT_YEAR column's date type.
            i tried doing this but still gives me the same error

            case when TO_DATE(SD.PROJ_DATE,'DD-MON-YYYY') between (TO_DATE('01-JAN-'||NEXT_YEAR,'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY')) THEN


            Pls bear and help me out

            Edited by: 776317 on Mar 4, 2013 8:28 PM
            • 3. Re: Date Conversion issue
              Another_user
              For comparisons and such, change all your strings to dates with the TO_DATE function. Never use or store dates as strings.

              The proper way to add a year to a date is ADD_MONTHS(sysdate,12).
              • 4. Re: Date Conversion issue
                Frank Kulash
                Hi,
                776317 wrote:
                Thx for the reply,
                Yes understand that but each and every column in that table would be of varchar2 type which cannot be helped as it is a seeded table.
                I can change the NEXT_YEAR column's date type.
                i tried doing this but still gives me the same error

                case when TO_DATE(SD.PROJ_DATE,'DD-MON-YYYY') between (TO_DATE('01-JAN-'||NEXT_YEAR,'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY')) THEN
                If proj_date is a 19-0character string, such as '2014/06/10 00:00:00', then you should convert all 19 characters in TO_DATE, e.g.
                WHEN  TO_DATE ( sd.proj_date
                              , 'YYYY/MM/DD HH24:MI:SS'
                              )  BETWEEN ...
                Alternatively, if you want to ignore the hours, minutes and seconds in proj_date, and only conver the first 10 characters, then
                WHEN  TO_DATE ( SUBSTR (sd.proj_date, 1, 10)
                              , 'YYYY/MM/DD'
                              )  BETWEEN ...
                Edited by: Frank Kulash on Mar 4, 2013 10:07 AM
                • 5. Re: Date Conversion issue
                  sb92075
                  776317 wrote:
                  Thx for the reply,
                  Yes understand that but each and every column in that table would be of varchar2 type which cannot be helped as it is a seeded table.
                  I can change the NEXT_YEAR column's date type.
                  i tried doing this but still gives me the same error

                  case when TO_DATE(SD.PROJ_DATE,'DD-MON-YYYY') between (TO_DATE('01-JAN-'||NEXT_YEAR,'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY')) THEN


                  Pls bear and help me out

                  Edited by: 776317 on Mar 4, 2013 8:28 PM
                  it is showing data as 2014/06/10 00:00:00
                  TO_DATE format mask does NOT match the content on the column as shown above.
                  • 6. Re: Date Conversion issue
                    chris227
                    Get rid of all those to_char and ||.Use oracle functions.
                    Works for me
                    select 
                    case when
                        sysdate
                        between trunc(sysdate, 'YEAR')
                        AND     add_months (trunc(sysdate, 'YEAR'), 3) - 1 THEN 100
                    else 200
                    end result
                    from dual 
                    
                    RESULT
                    "100"
                    • 7. Re: Date Conversion issue
                      ascheffer
                      If PROJ_DATE is a string in this format 2014/06/10 00:00:00, why are you trying to convert it to a date using 'DD-MON-YYYY' ????
                      Use to_date( PROJ_DATE, 'yyyy/mm/dd hh24:mi:ss' ) or to_date( PROJ_DATE, 'yyyy/dd/mm hh24:mi:ss' )
                      • 8. Re: Date Conversion issue
                        chris227
                        776317 wrote:
                        I have a column of varchar2 type but date is passed to it. the column name is proj_date coming from a table
                        it is showing data as 2014/06/10 00:00:00
                        That's not easy to understand.

                        But i guess it means: I store Dates as varchar2 in format YYYY/MM/DD and i am not interested in the time portion.
                        So oyu have to use ths format in the to_date function
                        instead of
                         TO_DATE(SD.PROJ_DATE,'DD-MON-YYYY')
                        
                         TO_DATE(SD.PROJ_DATE,'YYYY/MM/DD HH24:MI:SS')
                        Edited by: chris227 on 04.03.2013 07:15
                        Date mask corrected
                        • 9. Re: Date Conversion issue
                          779320
                          Dears,

                          I'm using this
                          TO_DATE(SD.PROJ_DATE,'YYYY/MM/DD') BETWEEN (TO_DATE('01-JAN-'||(NEXT_YEAR),'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY'))

                          But it still is throwing the same error.

                          I'm really lost on where am doing wrong.

                          I also tried

                          TO_DATE(SD.PROJ_DATE,''YYYY/MM/DD HH24:MI:SS') BETWEEN (TO_DATE('01-JAN-'||(NEXT_YEAR),'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY'))

                          still gives me the same error
                          • 10. Re: Date Conversion issue
                            779320
                            Its always the next year that should be taken into consideration.
                            the sysdate would be taken and the year should be incremented by 1 and the PROJ_DATES to be compared between first quarter dates
                            • 11. Re: Date Conversion issue
                              Paul  Horth
                              Please post exact SQL you are running and exact error message.
                              • 12. Re: Date Conversion issue
                                779320
                                This is the query am using

                                SELECT
                                EMPLOYEE_NUMBER
                                ,FULL_NAME
                                ,GRADE
                                ,NEW_GRADE
                                ,BASIC_PAY
                                ,case WHEN TO_DATE(SD.PROJ_DATE,'YYYY/MM/DD') BETWEEN (TO_DATE('01-JAN-'||(NEXT_YEAR),'YYYY/MM/DD')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'YYYY/MM/DD')) THEN
                                NEW_BASIC
                                ELSE (BASIC_PAY+BASIC_PAY*0.05)
                                END PROJECTED_BASICS
                                FROM XX_MRP_SALARY_BREAKUP_KP1 kp,
                                sit_date sd
                                where sd.emp_num(+)=kp.employee_number


                                The NEXT_YEAR COLUMN IS OF VARCHAR2 TYPE AND SAVING 2014 AS VALUE.

                                THE PROJ_DATE IS VARCHAR2 TYPE AND STORES 2014/06/10 00:00:00 AS VALUE.

                                Now I'm trying to see if the value is falling in between the first quarter of next year.

                                Edited by: 776317 on Mar 4, 2013 9:00 PM
                                • 13. Re: Date Conversion issue
                                  ascheffer
                                  select 
                                  case when to_date( PROJ_DATE, 'yyyy/mm/dd hh24:mi:ss' ) between add_months( trunc( sysdate, 'y' ), 12 )
                                                                                              and add_months( trunc( sysdate, 'y' ), 15 )
                                    then 100
                                    else 200
                                  end 
                                  from tab_a a,tab_b b
                                  where a.emp_num=b.employee_number
                                  or
                                  SELECT 
                                  EMPLOYEE_NUMBER
                                  ,FULL_NAME
                                  ,GRADE
                                  ,NEW_GRADE 
                                  ,BASIC_PAY
                                  , case when to_date( PROJ_DATE, 'yyyy/mm/dd hh24:mi:ss' ) between add_months( trunc( sysdate, 'y' ), 12 )
                                                                                              and add_months( trunc( sysdate, 'y' ), 15 )
                                    then NEW_BASIC
                                    else (BASIC_PAY+BASIC_PAY*0.05)
                                  end PROJECTED_BASICS
                                  FROM XX_MRP_SALARY_BREAKUP_KP1 kp,
                                  sit_date sd
                                  where sd.emp_num(+)=kp.employee_number
                                  Or
                                  SELECT 
                                  EMPLOYEE_NUMBER
                                  ,FULL_NAME
                                  ,GRADE
                                  ,NEW_GRADE 
                                  ,BASIC_PAY
                                  ,case WHEN TO_DATE(SD.PROJ_DATE,'yyyy/mm/dd hh24:mi:ss') BETWEEN TO_DATE('0101'||(NEXT_YEAR),'DDMMYYYY') AND TO_DATE('3103'||NEXT_YEAR,'DMMYYYY') THEN
                                  NEW_BASIC
                                  ELSE (BASIC_PAY+BASIC_PAY*0.05)
                                  END PROJECTED_BASICS
                                  FROM XX_MRP_SALARY_BREAKUP_KP1 kp,
                                  sit_date sd
                                  where sd.emp_num(+)=kp.employee_number
                                  Edited by: ascheffer on Mar 4, 2013 4:33 PM
                                  • 14. Re: Date Conversion issue
                                    chris227
                                    I dont know, why you dont like to use the oracle functions as shown to calculate the quater of the next year.
                                    However, the data mask is wrong again:
                                    case WHEN TO_DATE(SD.PROJ_DATE,'YYYY/MM/DD HH24:MI:SS') BETWEEN (TO_DATE('01-JAN-'||(NEXT_YEAR),'DD-MON-YYYY')) AND (TO_DATE('31-MAR-'||NEXT_YEAR,'DD-MON-YYYY')) THEN
                                    1 2 Previous Next