Weird behavior with casts to datetime in where clause

eiseli · #1 (**permalink**) 10-07-09, 07:44

Hello all,

Probably, I've misunderstood something but I can't explain this behavior. While the following SQL statement works (and returns 4 rows):

Code:

SELECT * FROM pr_sqlstats WHERE when = '10:24'::datetime HOUR TO MINUTE;

the following returns an empty result (while I would expect at least 4 but normally more):

Code:

SELECT * FROM pr_sqlstats WHERE when = '10'::datetime HOUR TO HOUR;

This code is being executed on a table which has been created as follows:

Code:

  CREATE TABLE pr_sqlstats (
    when DATETIME YEAR TO MINUTE DEFAULT CURRENT YEAR TO MINUTE,
    who CHAR(8) DEFAULT USER,
    grp SMALLINT,
    id SMALLINT
  );

Thanks for anybody who can point me to the right direction.

bigcalm · #2 (**permalink**) 10-09-09, 19:08

... WHERE when = '10'::datetime HOUR TO HOUR; ...

What this is actually doing is casting "10" to 10:00 effectively.

What you actually want is something of the order of...

where hour(when) = 10

Internet Services

Web Development

Digital Marketing

Consumer Tech