Is there an HOUR function?

[Dean Peddle]

Hi all:

I would like to be able to have an HOUR function that works on DATETIME
variables inthe same way that MONTH/DAY/YEAR does. For example:

SELECT * FROM mytable WHERE HOUR(mydate) = 10

Any suggestions on something that will provide similar capability? I'm
not looking for a programming solution - must be able to be done relatively
simply as an interactive SQL query

Thanks,
---------------------------------------------------------------------------
Dean Peddle | Opinions under | Just because you're not paranoid
dpe...@nlnet.nf.ca | construction - | doesn't mean they're not out to
| subject to change | get you!
(709)738-5717 | without notice. |
---------------------------------------------------------------------------

[Catalin Badea]

Dean Peddle (dpe...@nlnet.nf.ca) wrote:
: I would like to be able to have an HOUR function that works on DATETIME

: variables inthe same way that MONTH/DAY/YEAR does. For example:

: SELECT * FROM mytable WHERE HOUR(mydate) = 10

: Any suggestions on something that will provide similar capability? I'm
: not looking for a programming solution - must be able to be done relatively
: simply as an interactive SQL query

You can use the EXTEND function:

SELECT * FROM mytables WHERE EXTEND(mydate, HOUR TO HOUR) = "10"

Catalin.

===========================================================================

Catalin Badea _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/ _/ _/_/_/
_/ _/ _/ _/ _/_/ _/
Senior Technical Architect _/ _/_/ _/ _/_/_/ _/ _/_/_/
_/ _/ _/ _/ _/ _/
Tecsys Inc. _/ _/_/_/ _/_/_/ _/_/_/ _/ _/_/_/

====== c...@tecsys.com =========== include system "disclaimer.4gh" ======

[Mariusz D]

Dean Peddle (dpe...@nlnet.nf.ca) wrote:
: Hi all:

: I would like to be able to have an HOUR function that works on DATETIME
: variables inthe same way that MONTH/DAY/YEAR does. For example:
: SELECT * FROM mytable WHERE HOUR(mydate) = 10
: Any suggestions on something that will provide similar capability? I'm
: not looking for a programming solution - must be able to be done relatively
: simply as an interactive SQL query

select * from a_table where EXTEND(a_datetime_field, HOUR TO HOUR) = "10"

Ref : working with datetime fields, EXTEND, UNITS

Mariusz Malogrosz
mar...@tecsys.com

[Girish...@nsc.sprint.com]

Dean Peddle has written:

> I would like to be able to have an HOUR function that works on DATETIME
> variables inthe same way that MONTH/DAY/YEAR does. For example:
>
> SELECT * FROM mytable WHERE HOUR(mydate) = 10
>
> Any suggestions on something that will provide similar capability? I'm
> not looking for a programming solution - must be able to be done
> relatively simply as an interactive SQL query

Well, Dean there is a function called EXTEND which you can use in isql
selecting or comparing any datetime column. Read the reference manual for
details. The solution for your problem is listed here :

select *
from mytable
where extend(mydate, hour to hour) = extend("10", hour to hour)

Hope this helps.

Girish
-------------------------------------------------------------------------
Standard disclaimers apply.