PERL-SQL and date comparison

[Rafal Konopka]

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

 

[Ben Morrow]

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

 

[Gregory Toomey]

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

 

[Rafal Konopka]

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

 

[gnari]

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

 

[Rafal Konopka]

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]

Thanks gnari for the SQL function you suggested

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