You appear to have some errors in $query. I think "$start_time" should be "$start_time_field" and "$end_time" "$end_time_field". Perhaps the logic is ok in your dbms, but I'd write that (using placeholders) as, 

my $query = <<"ESQL";
SELECT * FROM $table
    WHERE $description_field LIKE '%$search_term%'
      and $start_time_field between ? and ?
      or $end_time_field between ? and ?
ESQL

[download]
In what follows you may need to apply an epoch time conversion function to $start_time_field and $end_time_field. If your dbms is forgiving and helpful with datetime formats, the following may not be necessary.

The surest way is to convert the dates and times to unix epoch time and do numerical comparisons. In Perl, 

use Time::Local;
# . . .

[download]
I'll put your start and end times into an array of (yr,mo,day,hr,min,sec) using a simpler regex, 
my @datetime_start = $date1 =~ /\d+/g;
$#datetime_start = 5;  # expand to six elements,
                       # even if there are only three

my @datetime_end = $date2 =~ /\d+/g;
$#datetime_end = 5;    # expand to six elements,
                       # even if there are only three

[download]
Now we can call timelocal() to get the range limits as epoch time, 
my $datetime_start = timelocal reverse @datetime_start;
my $datetime_end   = timelocal reverse @datetime_end;

[download]
All that remains is to compose the SQL query. You don't say what dbms you use, but most all will provide an epoch time conversion function for their native datetime type.

MySQL has a rich enough set of date and time functions that you don't need to jump through most of those hoops. Its comparison functions work fine with correctly formatted date strings. I think all you would need to do is $date1 =~ tr/()//d; and so on.

After Compline,
Zaxo

In reply to Re: Trying to select all records that match a datetime range. by Zaxo
in thread Trying to select all records that match a datetime range. by swares

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.