Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Question Unanswered: Weird behavior with casts to datetime in where clause

    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.

  2. #2
    Join Date
    Jul 2009
    Posts
    37
    ... 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •