PERL-SQL and date comparison

Discussion in 'Perl Misc' started by Rafal Konopka, Feb 13, 2004.

  1. I have a script that communicates with the SQL server (MS), and extracts the
    data from a list of fields in one or more tables. No problem works like a
    charm.

    The problem (actually just an annoyance) is the WHERE clause, where I want
    to extract the data only if the given field is equal to a certain date.

    I'm using Win32:ODBC module

    If my clause reads like:
    WHERE open_time='2/5/04/'
    or
    WHERE open_time='2004-02-05'

    (or any legal SQL format for that matter)

    nothing is retrieved. It seems that "=" fails. I checked with our SQL guys,
    and they claim that the '=' syntax should work.

    However, if I use BETWEEN, i.e.
    WHERE open_time BETWEEN '2/5/04 00:00:00' and '2004-02-05 23:59:59'

    I get what I want.

    Why is that? Since I want to compare three date fields, I'd rather use the
    "=" than three lengthy 'between' statements.

    Thanks,

    Rafal
     
    Rafal Konopka, Feb 13, 2004
    #1
    1. Advertising

  2. Rafal Konopka

    Ben Morrow Guest

    "Rafal Konopka" <> wrote:
    > I have a script that communicates with the SQL server (MS), and extracts the
    > data from a list of fields in one or more tables. No problem works like a
    > charm.
    >
    > The problem (actually just an annoyance) is the WHERE clause, where I want
    > to extract the data only if the given field is equal to a certain date.
    >
    > I'm using Win32:ODBC module
    >
    > If my clause reads like:
    > WHERE open_time='2/5/04/'
    > or
    > WHERE open_time='2004-02-05'
    >
    > (or any legal SQL format for that matter)
    >
    > nothing is retrieved. It seems that "=" fails. I checked with our SQL guys,
    > and they claim that the '=' syntax should work.
    >
    > However, if I use BETWEEN, i.e.
    > WHERE open_time BETWEEN '2/5/04 00:00:00' and '2004-02-05 23:59:59'
    >
    > I get what I want.
    >
    > Why is that? Since I want to compare three date fields, I'd rather use the
    > "=" than three lengthy 'between' statements.


    This is a problem with your database, not with Perl. Perl just passes
    the SQL straight to the database.

    At a guess, do you want WHERE open_time IS '2004/02/05' rather than =?

    Ben

    --
    Like all men in Babylon I have been a proconsul; like all, a slave ... During
    one lunar year, I have been declared invisible; I shrieked and was not heard,
    I stole my bread and was not decapitated.
    ~ ~ Jorge Luis Borges, 'The Babylon Lottery'
     
    Ben Morrow, Feb 13, 2004
    #2
    1. Advertising

  3. Rafal Konopka wrote:

    > I have a script that communicates with the SQL server (MS), and extracts
    > the data from a list of fields in one or more tables. No problem works
    > like a charm.
    >
    > The problem (actually just an annoyance) is the WHERE clause, where I want
    > to extract the data only if the given field is equal to a certain date.
    >
    > I'm using Win32:ODBC module
    >
    > If my clause reads like:
    > WHERE open_time='2/5/04/'
    > or
    > WHERE open_time='2004-02-05'
    >
    > (or any legal SQL format for that matter)
    >
    > nothing is retrieved. It seems that "=" fails. I checked with our SQL
    > guys, and they claim that the '=' syntax should work.
    >
    > However, if I use BETWEEN, i.e.
    > WHERE open_time BETWEEN '2/5/04 00:00:00' and '2004-02-05
    > 23:59:59'
    >
    > I get what I want.
    >
    > Why is that? Since I want to compare three date fields, I'd rather use
    > the "=" than three lengthy 'between' statements.
    >
    > Thanks,
    >
    > Rafal


    A very rough guesss ( www.dbforums.com is a better place to ask)

    The dates in question could be date/times

    eg dates were first stored internally as 2004-02-05 11:12:18
    so open_time='2004-02-05' will never match.

    SOLUTION:
    Store only the dates
    insert into table xxx(open_time) values ('2004-02-05')

    NOT insert into table xxx(open_time) values ('2004-02-05 11:12:18')


    gtoomey
     
    Gregory Toomey, Feb 13, 2004
    #3
  4. "Ben Morrow" <> wrote in message
    news:c0h6g5$2a4$...
    >
    > "Rafal Konopka" <> wrote:
    > > If my clause reads like:
    > > WHERE open_time='2/5/04/'
    > > [deleted]
    > > nothing is retrieved. It seems that "=" fails. I checked with our SQL

    guys,
    > > and they claim that the '=' syntax should work.
    > >
    > > However, if I use BETWEEN, i.e.
    > > WHERE open_time BETWEEN '2/5/04 00:00:00' and '2004-02-05

    23:59:59'
    > > deleted.

    >
    > This is a problem with your database, not with Perl. Perl just passes
    > the SQL straight to the database.


    That's what I thought.

    >
    > At a guess, do you want WHERE open_time IS '2004/02/05' rather than =?
    >
    > Ben


    I saw the SQL guys use the "=" syntax and it works. Perhaps it's the
    function of the interpreter.

    However, your suggestion 'IS' seems very promising. I'll try it tomorrow
    and let the group know if it works.

    Thanks,

    Rafal
     
    Rafal Konopka, Feb 13, 2004
    #4
  5. Rafal Konopka

    gnari Guest

    "Rafal Konopka" <> wrote in message
    news:eek:...
    > I have a script that communicates with the SQL server (MS), and extracts

    the
    > data from a list of fields in one or more tables. No problem works like a
    > charm.
    >
    > The problem (actually just an annoyance) is the WHERE clause, where I want
    > to extract the data only if the given field is equal to a certain date.
    >
    > I'm using Win32:ODBC module
    >
    > If my clause reads like:
    > WHERE open_time='2/5/04/'
    > or
    > WHERE open_time='2004-02-05'
    >
    > (or any legal SQL format for that matter)
    >
    > nothing is retrieved. It seems that "=" fails. I checked with our SQL

    guys,
    > and they claim that the '=' syntax should work.
    >
    > However, if I use BETWEEN, i.e.
    > WHERE open_time BETWEEN '2/5/04 00:00:00' and '2004-02-05

    23:59:59'
    >
    > I get what I want.
    >
    > Why is that? Since I want to compare three date fields, I'd rather use

    the
    > "=" than three lengthy 'between' statements.


    you are using a timestamp field to store a date.
    when you store '2004-02-05' what are you are actually storing?
    '2004-02-05 00:00:00' or '2004-02-05 12:00:00' ?

    you should be using functions like to_char():
    WHERE to_char(open_time,'YYYY-MM-DD')='2004-02-05'

    gnari

    P.S.: i do not know anything about MSSQL

    P.P.S.: never use date/time datatypes, use varchars with
    datetimes normalized like YYYYMMDDhhmmss
     
    gnari, Feb 13, 2004
    #5
  6. "Gregory Toomey" <> wrote in message
    news:c0h6o1$13quer$-berlin.de...
    > Rafal Konopka wrote:
    >
    > A very rough guesss ( www.dbforums.com is a better place to ask)
    >
    > The dates in question could be date/times
    >
    > eg dates were first stored internally as 2004-02-05 11:12:18
    > so open_time='2004-02-05' will never match.
    >

    See my reply to Ben. Invoked from a SQL client, it works,even though you're
    right, it is in datetime format.

    > SOLUTION:
    > Store only the dates
    > insert into table xxx(open_time) values ('2004-02-05')
    >
    > NOT insert into table xxx(open_time) values ('2004-02-05 11:12:18')
    >

    Unfortunately, I only have read access only.
    >
    > gtoomey


    Thanks,

    Rafal
     
    Rafal Konopka, Feb 13, 2004
    #6
  7. Thanks gnari for the SQL function you suggested

    > > you should be using functions like to_char():
    > > WHERE to_char(open_time,'YYYY-MM-DD')='2004-02-05'


    > > gnari


    The field I'm retrieving the date from is a datetime time stamp. Like I
    said when I use the SQL client "='2004-02-05'" works fine. but passed via a
    Perl script, it doesn't. Neither does 'IS'.

    I'll certainly try the to_char() function and see if it works.

    BTW, WHERE open_time BETWEEN '<date>' and <date>' certainly works!

    Thanks to all for pointers and suggestions. I really appreciate it.

    Rafal
     
    Rafal Konopka, Feb 14, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. darrel

    SQL Date comparison problem...

    darrel, Mar 23, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    13,069
    darrel
    Mar 24, 2005
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,278
    Michael Borgwardt
    May 30, 2004
  3. Diphay Z
    Replies:
    1
    Views:
    771
    Sudsy
    Dec 11, 2004
  4. Diphay Z
    Replies:
    1
    Views:
    7,984
    VisionSet
    Dec 11, 2004
  5. Diphay Z
    Replies:
    0
    Views:
    407
    Diphay Z
    Dec 11, 2004
Loading...

Share This Page