5 Replies Latest reply: Jun 19, 2014 5:30 AM by BluShadow RSS

    Querying date column defined as varcgar2(14)

    1037975

      Hi Guys,

       

      I have this column defined as varchar2(14).

      Is there anyway i can query out only records happening between 08:00 to 20:00?

      thanks

       

      Date

      ------

      20140514 01:00

      20140514 01:15

      20140514 01:30

      20140514 01:45

      20140514 02:00

      20140514 02:15

      20140514 02:30

      20140514 02:45

      20140514 03:00

      20140514 03:15

      20140514 03:30

      20140514 03:45

      20140514 04:00

      20140514 04:15

      20140514 04:30

      20140514 04:45

      20140514 05:00

      20140514 05:15

      20140514 05:30

      20140514 05:45

      20140514 06:00

      20140514 06:15

      20140514 06:30

      20140514 06:45

      20140514 07:00

      20140514 07:15

      20140514 07:30

      20140514 07:45

      20140514 08:00

      20140514 08:15

      20140514 08:30

      20140514 08:45

      20140514 09:00

      20140514 09:15

      20140514 09:30

      20140514 09:45

      20140514 10:00

      20140514 10:15

      20140514 10:30

      20140514 10:45

      20140514 11:00

      20140514 11:15

      20140514 11:30

      20140514 11:45

      20140514 12:00

      20140514 12:15

      20140514 12:30

      20140514 12:45

      20140514 13:00

      20140514 13:15

      20140514 13:30

      20140514 13:45

      20140514 14:00

      20140514 14:15

      20140514 14:30

      20140514 14:45

      20140514 15:00

      20140514 15:15

      20140514 15:30

      20140514 15:45

      20140514 16:00

      20140514 16:15

      20140514 16:30

      20140514 16:45

      20140514 17:00

      20140514 17:15

      20140514 17:30

      20140514 17:45

      20140514 18:00

      20140514 18:15

      20140514 18:30

      20140514 18:45

      20140514 19:00

      20140514 19:15

      20140514 19:30

      20140514 19:45

      20140514 20:00

      20140514 20:15

      20140514 20:30

      20140514 20:45

      20140514 21:00

      20140514 21:15

      20140514 21:30

      20140514 21:45

      20140514 22:00

      20140514 22:15

      20140514 22:30

      20140514 22:45

      20140514 23:00

        • 1. Re: Querying date column defined as varcgar2(14)
          Saravanan Vijayasundaram

          Hi,

          Please try,

          select

          to_date('20140514 08:00','YYYYMMDD HH24:MI') start_dt,

          to_date('20140514 20:00','YYYYMMDD HH24:MI') end_dt

          from dual;

           

           

           

          select *

          from table_name

          where (to_date(column_name,'YYYYMMDD HH24:MI') between to_date('20140514 08:00','YYYYMMDD HH24:MI')

          and to_date('20140514 20:00','YYYYMMDD HH24:MI'))

          • 2. Re: Querying date column defined as varcgar2(14)
            2683628

            CREATE

              TABLE datestring

              (

                datefield VARCHAR2(14)

              );

            INSERT

            INTO

              datestring

              (

                datefield

              )

              VALUES

              (

                '20140514 22:30'

              );

            INSERT

            INTO

              datestring

              (

                datefield

              )

              VALUES

              (

                '20140514 22:45'

              );

            INSERT

            INTO

              datestring

              (

                datefield

              )

              VALUES

              (

                '20140514 23:00'

              );

            COMMIT;

            SELECT

              TO_CHAR(datefield,'DD-MON-YYYY HH24:MI') AS required_date

            FROM

              (

                SELECT

                  to_date(datefield,'YYYYMMDD HH24:MI') AS datefield

                FROM

                  datestring

              )

            WHERE

              datefield BETWEEN to_date('14-MAY-2014 22:37','DD-MON-YYYY HH24:MI') AND

              to_date('14-MAY-2014 22:47','DD-MON-YYYY HH24:MI');

            • 3. Re: Querying date column defined as varcgar2(14)
              HuaMin Chen

              Try

              select *

              from tab1

              where substr(date_col,10,5) between '08:00' and '20:00';

              • 4. Re: Querying date column defined as varcgar2(14)
                Moazzam

                Try this:

                 

                SELECT *

                FROM datestring

                WHERE to_date(substr(datefield,9),'hh24:mi') BETWEEN to_date('08:00','hh24:mi') AND to_date('20:00','hh24:mi')

                • 5. Re: Querying date column defined as varcgar2(14)
                  BluShadow

                  The correct answer is to fix your data model.  DATEs should NOT be stored as strings, they should be stored as DATE, which is why oracle provides a DATE datatype secifically for that purpose.

                  The second correct answer is to conver the strings to DATE datatypes and then do your query on those using relevant DATE functionality in SQL.

                  Never treat dates as strings...

                   

                  Things like:

                   

                  where substr(date_col,10,5) between '08:00' and '20:00';

                   

                  is just wrong.  It may work in this instance, but if you, for example, wanted to search between 20:00 and 08:00 (ranging over midnight), then such comparisons would not work.  Doing range comparisons on strings is just wrong, and relies on implicit datatype conversions, or character by character comparisons.  DO NOT DO THAT.

                   

                  DATEs are DATEs.... so treat them like DATEs.

                   

                  If only people would learn to use datatypes correctly.