Results 1 to 8 of 8

Thread: Date Time Query

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Date Time Query

    Hi All,

    I have two columns in the table, one column stores date only and another column stores date and time. I am trying to right a query that would display date from one column and time from another column.
    Here are the columns.

    Date Date_Time
    04/28/04 05/03/04 11:00:00 AM
    05/30/04 04/24/04 12:30:00 PM
    02/20/04


    Please help. Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use TO_CHAR to pull out whatever interests you from each column - e.g. TO_CHAR(col,'HH24:MIS')

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    Sorry, I didn't specify that I need this in the where close, i.e.

    select date
    from table
    where date = 04/28/04(date from date column)||11:00 AM (time from date_time column)

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Before anything else, why did you split the date and time into two columns. A single column would have worked very nicely for that task. By default ALL date fileds contain a time element!!!

    That being said.


    select date from table
    where trunc(date) = to_date('04/28/2004','mm/dd/yyyy')
    and to_char(date_time,'HH24:MI') = '11:00';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2004
    Posts
    268
    I am trying to use instr to locate the first space in the date_time column, which would be the space between date and time, and then use substr to extract the time.

    select substr(instr(date_time,' ',1), 12)
    from table

    The instr however returns 0, which means that there is no space in the date_time column, but there is a space.

    Date_time column
    06/01/04 11:00:00 AM ( I coiped it straight from the DB)

    What am I doing wrong? Thanks.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are confusing a DATE, which is not a character string, with the formatted character version of that DATE that you happen to see when you select it.

    We have already told you how to get the time out of a DATE column using TO_CHAR(col,'HH24:MIS').

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by inka
    I am trying to use instr to locate the first space in the date_time column, which would be the space between date and time, and then use substr to extract the time.

    select substr(instr(date_time,' ',1), 12)
    from table

    The instr however returns 0, which means that there is no space in the date_time column, but there is a space.

    Date_time column
    06/01/04 11:00:00 AM ( I coiped it straight from the DB)

    What am I doing wrong? Thanks.
    a column of type date in an oracle database is a 7 byte binary value that holds the date and time. When you select a date field for display, sql*plus converts the date into a character string for DISPLAY only. The actual date field MUST be tested and used as a type date field, which means that you need to compare a date to a date or convert the date to a character string (varchar2) and then compare it to another string.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •