Comparing various time formats

Discussion in 'Databases' started by 5h4k42u1u, Oct 26, 2006.

0
  1. #1
    hey all, I've got a bit of a prob. I've just started maintaining an oracle database that someone else designed, and theres a field that stores time as a varchar. Now i'm trying to create a query to find records that have a time thats between a range.

    The problem that i have is that the times in the column are in differing formats (ie 8am, 8:00am, 08:00am). Can anyone tell me of a way to compare times taking into account the differing formats?

    thx in advance
     
    5h4k42u1u, Oct 26, 2006 IP
  2. rosytoes

    rosytoes Peon

    Messages:
    230
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You'll probably have to clean up this varchar and turn it into a nice timestamp before you can do anything useful with it.
     
    rosytoes, Oct 28, 2006 IP
  3. TheBorg

    TheBorg Peon

    Messages:
    144
    Likes Received:
    12
    Best Answers:
    0
    Trophy Points:
    0
    #3
    If PHP is available you can write a tool using the function strtotime(). It converts anything into a unix timestamp. For example: 
    strtotime("08:00am");
    Code (markup):
    or even: 
    strtotime("tomorrow");
    Code (markup):
    . You can also reformat the string like this: 
    date("Y-m-d H:i:s", strtotime($dbresult));
    Code (markup):
     
    TheBorg, Oct 29, 2006 IP