6 Replies Latest reply on Sep 28, 2013 5:20 PM by dariyoosh

    Query

    927578

      Hi,

       

       

      in my table i have data like

       

       

      birth_day      id

      ________________________

      25-04-1988                  1

      26-04-1988 23:59:00    2

       

       

      i want the data which has time stamp. i mean 2nd row(id=2). please help

        • 1. Re: Query
          Etbin

          where birth_day != trunc(birth_day) -- if birth_day is a date data type

           

          Regards

           

          Etbin

           

          Message was edited by: Etbin

           

          select to_char(birth_day,'dd-mm-yyyy hh24:mi:ss') birth_day,id

            from (select to_date('25-04-1988','dd-mm-yyyy') birth_day,1 id from dual union all

                  select to_date('26-04-1988 23:59:00','dd-mm-yyyy hh24:mi:ss'),2 from dual

                 )

          where birth_day != trunc(birth_day)

           

          BIRTH_DAYID
          26-04-1988 23:59:002
          • 2. Re: Query
            Frank Kulash

            Hi,

            927578 wrote:

             

            Hi,

             

             

            in my table i have data like

             

             

            birth_day      id

            ________________________

            25-04-1988                  1

            26-04-1988 23:59:00    2

             

             

            i want the data which has time stamp. i mean 2nd row(id=2). please help

            What is the data type of birth_day?  Is it a DATE?

            Oracle DATEs always include hours, minutes and seconds.  If they are not given when the DATE is created (for example

             

            TO_DATE (25-04-1988, 'DD-MM-YYYY')

            ) then they default to 00:00:00.   Etbin showed the best way of telling if a DATE has hours. minutes and/or seconds other than the default 00:00:00.

            1 person found this helpful
            • 3. Re: Query
              dariyoosh

              Hi,

               

              FrankKulash wrote:

                Etbin showed the best way of telling if a DATE has hours. minutes and/or seconds other than the default 00:00:00.

               

              So, I think OP's question "i want the data which has time stamp" has no meaning (or maybe I misunderstand the topic), the real question is what is the set of time values that I'm looking for (or I want to exclude) given the fact that for a DATE column the value 00:00:00 could actually be a valid value specified by the user  provided in the INSERT statement (and therefore not a default value considered by oracle)

               

              Regards,

              Dariyoosh

              1 person found this helpful
              • 4. Re: Query
                927578

                Thanks all..

                 

                data type is DATE.

                • 5. Re: Query
                  Etbin

                  Once a date has been stored, Oracle cannot distinguish whether the time part 00:00:00 has been specified or not.

                   

                  select id,to_char(birth_day,'dd-mm-yyyy hh24:mi:ss') birth_day,dump(birth_day,16) birth_day_dump

                    from (select 1 id,to_date('25-04-1988','dd-mm-yyyy') birth_day from dual union all

                          select 2,to_date('25-04-1988 00:00:00','dd-mm-yyyy hh24:mi:ss') from dual

                         )

                   

                  IDBIRTH_DAYBIRTH_DAY_DUMP
                  125-04-1988 00:00:00Typ=12 Len=7: 77,bc,4,19,1,1,1
                  225-04-1988 00:00:00Typ=12 Len=7: 77,bc,4,19,1,1,1

                   

                  Regards

                   

                  Etbin

                  • 6. Re: Query
                    dariyoosh

                    Etbin wrote:

                     

                    Once a date has been stored, Oracle cannot distinguish whether the time part 00:00:00 has been specified or not.

                     

                    Yes, I agree, I shouldn't have written in my previous comment :

                     

                    ". . . and therefore not a default value considered by oracle . . ."

                     

                    What I wanted to say (and it seems that I failed to say it correctly ) is that depending on the problem and the context, 00:00:00, itself, can be a value of interest (it is not forbidden to be born at 00:00:00 ).

                     

                    Therefore, from the concept of a DATE, saying that I want to query those DATE that have or don't have time portion has no meaning, what we do is just filtering on those values (which will be default if the user has not specified)

                     

                    Regards,

                    Dariyoosh