1 2 Previous Next 17 Replies Latest reply: Apr 18, 2012 8:55 AM by Paul Horth RSS

    Doubt in char datatype for date

    903490
      Hello everybody,
      Can anybody solve my issue. I am new to this field. I am unable to solve.

      In table market, I want to find those records where purchase_date >selling_date. Please note that both variable had varchar2 data type.

      Purchase_date Selling_date
      MAR-2008 APR-2008
      MAY-2009 MAR-2009
      23-MAY-2009 21-MAY-2009
        • 1. Re: Doubt in char datatype for date
          Paul  Horth
          Dates should be stored in date fields, not varchar2. Then you wouldn't have any problems.

          A simple
          select *
          from market
          where purchase_date > selling_date
          would work.

          I'm not going to tell you how to solve the original problem and I plead with
          others not to, because it is WRONG to store dates in varchar2 fields
          and you are just storing up trouble for the future.

          Edited by: Paul Horth on 18-Apr-2012 03:36

          Edited by: Paul Horth on 18-Apr-2012 03:36
          • 2. Re: Doubt in char datatype for date
            JustinCave
            1) You should never store dates in a VARCHAR2 column.
            2) Are those the only two formats stored in the table? Or are there more string formats in the table?

            Justin
            • 3. Re: Doubt in char datatype for date
              Frank Kulash
              Hi,

              Assuming all strings are either 11 characters already (in 'DD-MON-YYYY' format), or 8 characters ('MON-YYYY' format):
              SELECT     *
              FROM     market
              WHERE     TO_DATE ( LPAD (purchase_date, 11, '01-')
                        , 'DD-MON-YYYY'
                        ) >
                   TO_DATE ( LPAD (selling_date,  11, '01-')
                        , 'DD-MON-YYYY'
                        )
              ;
              This treats 'APR-2012' as '01-APR-2012'.

              If your data is less predictable, then you may need PL/SQL.

              Storing date information in VARCHAR2 columns is not a very good idea. This problem shows just one of the reasons. Use DATE columns for date informatin.

               

              I hope this answers your question.
              If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
              Point out where the query above is getting the wrong results, and explain, using specific examples, how you get those results from that data in those places.
              Always say what version of Oracle you're using.
              • 4. Re: Doubt in char datatype for date
                908002
                expecting the data in the table will be stored in the either mon-yyyy or dd-mon-yyyy
                decode(length(purchase_Date),8, to_Date(purchase_Date,'MM-YYYY'),11,to_Date(purchase_Date,'dd-MM-YYYY')) >decode(length(selling_Date),8, to_Date(selling_Date,'MM-YYYY'),11,to_Date(selling_Date,'dd-MM-YYYY'))
                • 5. Re: Doubt in char datatype for date
                  V prasad
                  CREATE TABLE TEST(Purchase_date VARCHAR2(12), Selling_date VARCHAR2(12), TNO NUMBER(1));
                  INSERT INTO TEST VALUES('MAR-2008', 'APR-2008',1);
                  INSERT INTO TEST VALUES('MAY-2009', 'MAR-2009',2);
                  INSERT INTO TEST VALUES('23-MAY-2009', '21-MAY-2009',3);
                  
                  
                  SELECT * FROM TEST;
                  
                  SELECT * FROM TEST 
                  WHERE DECODE(LENGTH(TRIM(Purchase_date)),8,Purchase_date) >= DECODE(LENGTH(TRIM(Selling_date)),8,Selling_date) 
                  OR DECODE(LENGTH(TRIM(Purchase_date)),11,Purchase_date) >= DECODE(LENGTH(TRIM(Selling_date)),11,Selling_date)
                  
                  
                  PURCHASE_DATE SELLING_DATE TNO                    
                  ------------- ------------ ---------------------- 
                  MAR-2008      APR-2008     1                      
                  MAY-2009      MAR-2009     2                      
                  23-MAY-2009   21-MAY-2009  3                      
                  
                  3 rows selected
                  
                  
                  Is this your are excepted result ????
                  • 6. Re: Doubt in char datatype for date
                    903490
                    This one is correct. But if the date are like this 31-MAR-2006 00:00, can I use same length and decode function
                    • 7. Re: Doubt in char datatype for date
                      903490
                      hi, the SQL is not working for last record. It only compares the date, not month and even year. For record where purchase date is ('20-FEB-2009') and Selling_date (15-APR-2010), it compares days 20>15 , cond true but not compare month or even year.

                      Is it possible to solve


                      CREATE TABLE TEST_ashok1(Purchase_date VARCHAR2(40), Selling_date VARCHAR2(40), TNO NUMBER(1));
                      INSERT INTO TEST_ashok1 VALUES('MAR-2008', 'APR-2008',1);
                      INSERT INTO TEST_ashok1 VALUES('MAY-2009', 'MAR-2009',2);
                      INSERT INTO TEST_ashok1 VALUES('05-MAY-2009 00:00', '02-MAY-2009 00:00',4);
                      INSERT INTO TEST_ashok1 VALUES('20-FEB-2009', '15-APR-2010',7);

                      select * from test_ashok1
                      WHERE DECODE(LENGTH(TRIM(Purchase_date)),8,Purchase_date) > DECODE(LENGTH(TRIM(Selling_date)),8,Selling_date)
                      OR DECODE(LENGTH(TRIM(Purchase_date)),11,Purchase_date) > DECODE(LENGTH(TRIM(Selling_date)),11,Selling_date)
                      OR DECODE(LENGTH(TRIM(Purchase_date)),17,Purchase_date) > DECODE(LENGTH(TRIM(Selling_date)),17,Selling_date)
                      • 8. Re: Doubt in char datatype for date
                        V prasad
                        SELECT * FROM TEST 
                        WHERE (CASE WHEN LENGTH(Purchase_date) = 8 THEN Purchase_date
                                    WHEN LENGTH(Purchase_date) = 12 THEN Purchase_date 
                                    ELSE
                                    Purchase_date END)>=
                              (CASE WHEN LENGTH(Selling_date) = 8 THEN Selling_date
                                    WHEN LENGTH(Selling_date) = 12 THEN Selling_date 
                                    ELSE
                                    Selling_date END);
                        
                        PURCHASE_DATE SELLING_DATE TNO                    
                        ------------- ------------ ---------------------- 
                        MAR-2008      APR-2008     1                      
                        MAY-2009      MAR-2009     2                      
                        23-MAY-2009   21-MAY-2009  3                      
                        
                        3 rows selected
                        Edited by: OraclePLSQL on Apr 18, 2012 5:15 PM
                        • 9. Re: Doubt in char datatype for date
                          V prasad
                          CREATE TABLE TEST_ashok1(Purchase_date VARCHAR2(40), Selling_date VARCHAR2(40), TNO NUMBER(1));
                          INSERT INTO TEST_ashok1 VALUES('MAR-2008', 'APR-2008',1);
                          INSERT INTO TEST_ashok1 VALUES('MAY-2009', 'MAR-2009',2);
                          INSERT INTO TEST_ashok1 VALUES('05-MAY-2009 00:00', '02-MAY-2009 00:00',4);
                          INSERT INTO TEST_ashok1 VALUES('20-FEB-2009', '15-APR-2010',7);
                          
                          
                          SELECT * FROM TEST_ashok1 
                          WHERE (CASE WHEN LENGTH(Purchase_date) = 8 THEN Purchase_date
                                      WHEN LENGTH(Purchase_date) = 12 THEN Purchase_date 
                                      ELSE
                                      Purchase_date END)>=
                                (CASE WHEN LENGTH(Selling_date) = 8 THEN Selling_date
                                      WHEN LENGTH(Selling_date) > 12 THEN Selling_date 
                                      ELSE
                                      Selling_date END);
                          
                          
                          PURCHASE_DATE                            SELLING_DATE                             TNO                    
                          ---------------------------------------- ---------------------------------------- ---------------------- 
                          MAR-2008                                 APR-2008                                 1                      
                          MAY-2009                                 MAR-2009                                 2                      
                          05-MAY-2009 00:00                        02-MAY-2009 00:00                        4                      
                          20-FEB-2009                              15-APR-2010                              7                      
                          
                          4 rows selected
                          • 10. Re: Doubt in char datatype for date
                            903490
                            The last record should not come if you check logically. See here purchase date(20-FEB-2009) <selling date(15-APR-2010) . chk the year.
                            But our data retrieval is reverse. This record should skip as purchase date is not greater than selling date.
                            • 11. Re: Doubt in char datatype for date
                              Igor.M
                              ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN';
                              drop TABLE TEST_ashok1;
                              CREATE TABLE TEST_ashok1(Purchase_date VARCHAR2(40), Selling_date VARCHAR2(40), TNO NUMBER(1));
                              INSERT INTO TEST_ashok1 VALUES('MAR-2008', 'APR-2008',1);
                              INSERT INTO TEST_ashok1 VALUES('MAY-2009', 'MAR-2009',2);
                              INSERT INTO TEST_ashok1 VALUES('05-MAY-2009 00:00', '02-MAY-2009 00:00',4);
                              INSERT INTO TEST_ashok1 VALUES('20-FEB-2009', '15-APR-2010',7);
                              
                              select * from test_ashok1
                              WHERE decode (length(Purchase_date) ,  8 , to_date(Purchase_date, 'MON-YYYY')
                                                                                  , 11 , to_date(Purchase_date, 'DD-MON-YYYY')
                                                                                  , 17 , to_date(Purchase_date, 'DD-MON-YYYY HH24:Mi') 
                                                                                  , null ) >
                                          decode( length(Selling_date) , 8 , to_date(Selling_date, 'MON-YYYY')
                                                                                  , 11 , to_date(Selling_date, 'DD-MON-YYYY')
                                                                                  , 17 , to_date(Selling_date, 'DD-MON-YYYY HH24:Mi')
                                                                                  , null ) ;
                              
                              PURCHASE_DATE                            SELLING_DATE                                    TNO
                              ---------------------------------------- ---------------------------------------- ----------
                              MAY-2009                                 MAR-2009                                          2
                              05-MAY-2009 00:00                        02-MAY-2009 00:00                                 4
                              • 12. Re: Doubt in char datatype for date
                                Stew Ashton
                                My apologies to OraclePLSQL, but his solution is the only one you should not use.

                                You should use one of the solutions that does TO_DATE and then compares dates.

                                Start with this:
                                select purchase_date from <table>
                                union
                                select selling_date from <table>
                                Then figure out all the different date formats that are being used.

                                Then do a TO_DATE using a conditional format string, for example:
                                SELECT
                                TO_DATE(
                                  PURCHASE_DATE,
                                  DECODE(LENGTH(PURCHASE_DATE), 8, 'MON-YYYY', 'DD-MON-YYYY')
                                ) PURCHASE_DATE
                                FROM MARKET
                                If you need help with the "conditional format string", tell us all the different data formats that are being used.
                                • 13. Re: Doubt in char datatype for date
                                  padders
                                  CASE
                                     WHEN LENGTH (purchase_date) = 8 THEN purchase_date
                                     WHEN LENGTH (purchase_date) = 12 THEN purchase_date
                                     ELSE purchase_date
                                  END
                                  We'll be in touch.
                                  • 14. Re: Doubt in char datatype for date
                                    V prasad
                                    Try This.. to convert data into date datatype.
                                    
                                    
                                    SELECT * FROM TEST_ashok1 
                                    WHERE TO_DATE(DECODE(LENGTH(TRIM(Purchase_date)),8,Purchase_date),'MON-YYYY') >= 
                                    TO_DATE(DECODE(LENGTH(TRIM(Selling_date)),8,Selling_date),'MON-YYYY')
                                    OR TO_DATE(DECODE(LENGTH(TRIM(Purchase_date)),11,Purchase_date),'DD-MON-YYYY') >= 
                                    TO_DATE(DECODE(LENGTH(TRIM(Selling_date)),11,Selling_date),'DD-MON-YYYY')
                                    OR (CASE WHEN LENGTH(Purchase_date) > 11 THEN Purchase_date
                                                 END) >=
                                          (CASE WHEN LENGTH(Selling_date) >11 THEN Selling_date
                                                END);
                                    1 2 Previous Next