DATETIME query

Posted in 2003

Summary

A user on IDS 9.30 (HP-UX) wanted a CASE expression flagging rows as 'BATCH' or 'IMM' based on the hour part of a DATETIME column, but couldn't get the syntax right. Replies identified two faults: DATETIME() was being misused (EXTEND(col, HOUR TO HOUR) is the correct way to narrow precision), and the hour must be compared to a DATETIME literal such as DATETIME(20) HOUR TO HOUR rather than an integer. Also, the condition needed OR, not AND, since no hour is both >20 and <4. Jonathan Leffler posted a working CASE expression; a suggested HOUR() function was retracted as non-existent.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: SQL Development & Query Writing, Platform-Specific Issues, Versions, Editions & End-of-Life

KEVIN STRUCKHOFF — Wed, 01 Oct 2003 16:49:59 — source: IIUG Forums & Mailing Lists

Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. Here is my sql: select via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN ( (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) THEN 'BATCH' ELSE ' IMM' ) END, via_ord_tot_items from viaware_trans where via_trans_code = 'OMC' and via_trans_date > '09-01-2003' and via_whse_nbr = '15' Any ideas welcome. TIA. Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com

↪ replying to KEVIN STRUCKHOFF

Stephanie_Peltier@txnp.uscourts.gov — Wed, 01 Oct 2003 18:06:37 — source: IIUG Forums & Mailing Lists

This is a multipart message in MIME format. --=_related 006331B386256DB2_= Content-Type: multipart/alternative; boundary="=_alternative 006331B686256DB2_=" --=_alternative 006331B686256DB2_= Content-Type: text/plain; charset="us-ascii" Try the EXTEND function to limit your DATETIME value to hour only: CASE When EXTEND(via_trans_dte_time),hour to hour) > 20 etc See if that works for you! "KEVIN STRUC...." <kevinstruckhoff@yahoo.com> Sent by: forum.subscriber@iiug.org 10/01/2003 11:49 AM To: ids@iiug.org cc: Subject: DATETIME query [1966] Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. Here is my sql: select via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN ( (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) THEN 'BATCH' ELSE ' IMM' ) END, via_ord_tot_items from viaware_trans where via_trans_code = 'OMC' and via_trans_date > '09-01-2003' and via_whse_nbr = '15' Any ideas welcome. TIA. Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com --=_alternative 006331B686256DB2_= Content-Type: text/html; charset="us-ascii" Try the EXTEND function to limit your DATETIME value to hour only: CASE         When  EXTEND(via_trans_dte_time),hour to hour) > 20               etc See if that works for you! <img src=cid:_1_1A8000005C00006331B386256DB2> <table width=100%> <tr valign=top> <td> <td>"KEVIN STRUC...." <kevinstruckhoff@yahoo.com> Sent by: forum.subscriber@iiug.org 10/01/2003 11:49 AM <td>                To:        ids@iiug.org         cc:                 Subject:        DATETIME query  [1966]</table> Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. Here is my sql: select via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE                     WHEN (      (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 )     THEN 'BATCH'      ELSE ' IMM'   ) END, via_ord_tot_items from viaware_trans where via_trans_code = 'OMC' and via_trans_date > '09-01-2003' and via_whse_nbr = '15' Any ideas welcome. TIA. Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com --=_alternative 006331B686256DB2_=-- --=_related 006331B386256DB2_= Content-Type: image/gif Content-ID: <_1_1A8000005C00006331B386256DB2> Content-Transfer-Encoding: base64 R0lGODlh/wGPAOcAAAAAAP////j48AAAgAAAQCAggCAgQCAAgMDYwAAgQAAggCAAQKDI8MDAwAAA wCAgwEAggEBAgP7/+v/d3S4W3d3IIgAAhAIAAGJvb2ttYXJrLm5zZgAA/v/6/93d+v/d3RwAAABm D/7/P5BMAOVrJYYAAAAARGF0YWJhc2VzAP7/+v/d3S4W3d3IIgAAPAIAAKDI8ADAwMAAAADAACAg wABAIIAAQECAAAAAAAD///8A+PjwAAAAgAAAAEAAICCAACAgQAAgAIAAwNjAAAAgQAAAIIAAIABA AKDI8ADAwMAAAADAACAgwABAIIAAQECAAP8AAAAAgAAAAAAAAP///wCAAIAAAAD/AP7/AQH6/93d Lhbd3cgiAACwAQAAAAMYAAAAAAD///8A+PjwAAAAgAAAAEAAICCAACAgQAAgAIAAwNjAAAAgQAAA IIAAIABAAKDI8ADAwMAAAADAACAgwABAIIAAQECAAP8AAAAAgAAAAAAAAP///wCAAIAAAAD/AAEB AQH+/wEB+v/d3S4W3d3IIgAANAEAAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB AQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB AQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEB AQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEA/v/DAPr/3d0uFt3dyCIAAHAAAAABwgACAQDCAQEA wwEBAMIBAQDDAcMAxwEBAMkBwwDDAQEAwgHCAMIBAQDDAcMAwwEBAEAyAMYBQAMAxQEBAEA8AAEB QAMAxQEBAEA8AAEBQAMAxQEBAEA8AAEBAf7/AQH6/93d+v/d3TAEAAAUB/7/KAAAACAAAAAgAAAA AQAIAAAAAAAAAAAAAAAAAAAAAAAAAQAAAAAAAAAAAAAAAIAAAIAAACwAAAAA/wGPAEAI/wADCBxI sKDBgwgTKlzIsKHDhxAjSpxIsaLFixgzatzIsaPHjxgBFBQZgORDkx9RlkxIUiXIlzBjypxJs6bN mzglAti5c2BPgTx9ljRJ1KfIlkJXAv05kiXTlkiXFs1JtarVq1izasWJ0iVBr0lTKjUINmPZplvT ql3Ltq1bhTyZLlX69KjUrnalfpUL9CDUn1Pp9h1MtO7guVCNBn7LuLHjx5AZqjw7GS7ahWfDRt7M ubPnz1zlgo27WPPXvYVF5wWsOGxm0LBjy56tVnTD0he73o74+vBl2sCDCx+OsLLew6yRP3VNNu7p 40ORt57bHLfvoYELjw1KvLv37zV1U//f+7u0eM1R0Y9dD769+/fwSZ9/Pv+uYpfy7WPnzh2+//8A BmecTOfxFd1zB+pWn3gDrmdggBBGKKFlxeUVXWrMOeWcaYT5dd901aWGIVkTlmjiiSimqOKKKQqA EAEwwlhAjDQSQJCLAeCIYwAG0GjAjzveGGQANdYopEAJENCjkkXGuCSMAu24ZAIzGinAji5mmWOO V26po45e3sjimGRadaUACBBJwAAFsKmlAD0OIOeMB60p5wBXElBAlQlweSaPd+7p559i4ohAly7G OMABBCxggIuHdrmlkncOQEACSfbI5aA4/tRAlEPt2FNPAgRV6qMuysefdnSNauCrf1H/Z9hJ+Bl1 4K22jYcdZuO5ShqucKmaHmCx9neXsbbuSmyyyEqGn4jMHjWZrxuSh1hi+/1VbUN/yninjVmeKa6k RLb5wAGVWlqpnnQOZOe6oMJpZwELtItolgZYWmWU4goEo5xMDuQiA4FaSsC9Q271YJlrLVzRtgxH LPHEFFds8cUYZ+zRctJt1zF9SQ0bIseCeWxybxqnrPLKLLfsMmcNdkiiySBf5lV2IWcocskI3vxx ei8HLfTQRBe9csxOYfbrzx/nLDPPKBst9dRUV221ylHtjPPTO7PHXtYznxx2z9epJ53PXl+t9tps t+32TQ127SCCHDp9HYZRv6333nz3/+33W/nNPNXgOpXHdNrFGs7gqE//7fjjkEcu+eSUV/4RoQSg C7Ci6VpqgMCSCoDuAZp/q1CS79LYeaNbAvpv55WSbrC7bXoOasEDLBDulaSSRy5QnwPw+a663loc rc2ltHTxxgqrrbTLo0bshstDnLy1FTokLFWBQ6drXc9TO1q1S1dvPUNB2ll67IHyS/ucB09a6aOt 8/iumwh92fqfAtyP56CgExi6aienAeqLX6E7CJbCFCQtgYov4OPPsW6Wn2X1qnwagtizLhii+1CQ eiAkX39gNRIRYat7yRqZCT2IreKxcFoldGEEodesY33IhtnKG6gytyiAbepI7iogm//AVZD+EdBS +yPS+no4J9LZKEqRulG+NEe/+gUgTeUS4p1Gx64ncsqBlgujGMdIxjKa8YxoTKMai/ZBEElLKKop CslqOKAREm9EK9HhGvfoFuPghnBc6xjQxka3QHpNj3xM5GwQqchGOvKRkCxTHafnRuM1L44s+dpq EkS4OVIyj8cZ4XIYGclI+tFugISa4CiEysNpbZWu1FkpZ0nLWtrylrgMGtIKySuQxaovmLxjJR2W y2LmZJemoczxBNlKsRnSmNCMpjSnSc1qSpJmdqSeJUeZR7Sh8pN2GREdP/kb4mEzmNbcoxyZGUub ceiUaNlarcI2SLM1ZWu8TKc+98n/z37605oLmluGMpnJzFgobf9MaEg8NMGmERJxBEWoRBVK0Ypa 9KIYxegk8bIdbkYtoAWCaEDhOLZ1ZrSUo2nmPLXH0BwSJnpli9

↪ replying to KEVIN STRUCKHOFF

Steve Willcoxon — Wed, 01 Oct 2003 18:36:29 — source: IIUG Forums & Mailing Lists

I imagine you cannot find anything > 20 AND < 4. I didn't look at rest of statement, but know the AND should be OR (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 ****and should be or **** DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) Good luck Steve ----- Original Message ----- From: "KEVIN STRUC...." <kevinstruckhoff@yahoo.com> To: <ids@iiug.org> Sent: Wednesday, October 01, 2003 12:49 PM Subject: DATETIME query [1966] Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. Here is my sql: select via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN ( (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) THEN 'BATCH' ELSE ' IMM' ) END, via_ord_tot_items from viaware_trans where via_trans_code = 'OMC' and via_trans_date > '09-01-2003' and via_whse_nbr = '15' Any ideas welcome. TIA. Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com

↪ replying to KEVIN STRUCKHOFF

Mark D. Stock — Wed, 01 Oct 2003 19:44:38 — source: IIUG Forums & Mailing Lists

KEVIN STRUC.... wrote: > Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. > > I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. > > Here is my sql: > > select > via_whse_nbr, > via_ord_sls_pgm, > via_ord_dlr_type, > via_trans_dte_time, > CASE > WHEN ( > (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) > THEN 'BATCH' > ELSE ' IMM' > ) > END, > via_ord_tot_items > from viaware_trans > where via_trans_code = 'OMC' > and via_trans_date > '09-01-2003' > and via_whse_nbr = '15' > > Any ideas welcome. Mmm, well several points spring to mind: 1. The DATETIME() function is used to convert a value to a DATETIME, so you are trying to convert a DATETIME to a DATETIME. You need to use the EXTEND function to extend the precision of your DATETIME to HOUR TO HOUR. 2. You cannot easily convert a DATETIME to an INTEGER value, which is what you will need in order to compare the INT values 4 & 20. 3. Your WHEN condition will never be true, as far as I can see. The hour cannot be larger than 20 AND smaller than 4. Perhaps you mean between 4 and 20? Or even NOT between 4 & 20? So, a possible solution may be: SELECT via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN EXTEND(via_trans_dte_time, HOUR TO HOUR) > DATETIME(4) HOUR TO HOUR AND EXTEND(via_trans_dte_time, HOUR TO HOUR) < DATETIME(20) HOUR TO HOUR THEN 'BATCH' ELSE ' IMM' END, via_ord_tot_items FROM viaware_trans WHERE via_trans_code = 'OMC' AND via_trans_date > '09-01-2003' AND via_whse_nbr = '15' Or even: SELECT via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN EXTEND(via_trans_dte_time, HOUR TO HOUR) > DATETIME(20) HOUR TO HOUR OR EXTEND(via_trans_dte_time, HOUR TO HOUR) < DATETIME(4) HOUR TO HOUR THEN 'BATCH' ELSE ' IMM' END, via_ord_tot_items FROM viaware_trans WHERE via_trans_code = 'OMC' AND via_trans_date > '09-01-2003' AND via_whse_nbr = '15' Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to KEVIN STRUCKHOFF

June Hunt — Wed, 01 Oct 2003 20:08:38 — source: IIUG Forums & Mailing Lists

KEVIN STRUC.... <kevinstruckhoff@yahoo.com> wrote: >Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. > >I am trying to set a constant in a select statement based on the hour of a >datetime column, but I can't seem to find the correct syntax to test the >'hour'. > >Here is my sql: > >select > via_whse_nbr, > via_ord_sls_pgm, > via_ord_dlr_type, > via_trans_dte_time, > CASE > WHEN ( > (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME >(via_trans_dte_time) HOUR TO HOUR < 04 ) > THEN 'BATCH' > ELSE ' IMM' > ) > END, > via_ord_tot_items >from viaware_trans >where via_trans_code = 'OMC' > and via_trans_date > '09-01-2003' > and via_whse_nbr = '15' > >Any ideas welcome. > I first tried to get this to work by using 'EXTEND'. I was getting a 1260 error (It is not possible to convert between the specified types) when comparing the result of the EXTEND and the numeric values of 4 and 20. I've tried any number of combinations where I would compare the result to another 'EXTEND... HOUR TO HOUR' value. I kept coming up with syntax errors. The best I can come up with is the following test code (run via dbaccess): create table viaware_trans (via_whse_nbr smallint, via_trans_dte_time datetime year to minute,via_ord_tot_items smallint); insert into viaware_trans values (1, "2003-01-07 18:40", 3); insert into viaware_trans values (1, "2003-01-07 22:10", 1); insert into viaware_trans values (1, "2003-01-08 03:25", 2); insert into viaware_trans values (1, "2003-01-08 06:15", 3); select via_whse_nbr, via_trans_dte_time, (EXTEND(TODAY, YEAR TO MINUTE)) as stime, (EXTEND(TODAY, YEAR TO MINUTE)) as etime,via_ord_tot_items from viaware_trans into temp xxx; update xxx set stime = stime + INTERVAL(04:00) HOUR TO MINUTE where 1=1; update xxx set etime = etime + INTERVAL(20:00) HOUR TO MINUTE where 1=1; select via_whse_nbr,CASE WHEN (EXTEND(via_trans_dte_time, HOUR TO HOUR) < EXTEND(stime, HOUR TO HOUR) or EXTEND(via_trans_dte_time, HOUR TO HOUR) > EXTEND(etime, HOUR TO HOUR)) THEN ' IMM' ELSE 'BATCH' END, via_ord_tot_items from xxx; And the results were as follows: via_whse_nbr (expression) via_ord_tot_items 1 BATCH 3 1 IMM 1 1 IMM 2 1 BATCH 3 Not exactly what you were looking for, but perhaps this will give you some ideas. -- June Hunt _________________________________________________________________ Get McAfee virus scanning and cleaning of incoming attachments. Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es

↪ replying to KEVIN STRUCKHOFF

Jonathan Leffler — Thu, 02 Oct 2003 02:53:12 — source: IIUG Forums & Mailing Lists

As others mentioned, EXTEND is a major part of the solution - in its role as a contraction rather than extension operator. The other operator is the DATETIME constructor on the RHS of the comparison. CASE WHEN (EXTEND(via_trans_dte_time, HOUR TO HOUR) > DATETIME(20) HOUR TO HOUR OR EXTEND(via_trans_dte_time, HOUR TO HOUR) < DATETIME(4) HOUR TO HOUR) THEN 'BATCH' ELSE 'IMM' END Note the use of the DATETIME literals and also the change of conjunction from AND (always false) to OR (spotted by Steve, too, I see). I also reworked some extraneous parentheses. At least, a transcription of the above worked correctly. If you want the shorter 4 UNITS HOUR notation, you have to convert via_trans_dte_time to an interval (since midnight on the day in question). That is actually harder work than the comparisons shown. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "KEVIN STRUC...."| | | <kevinstruckhoff@| | | yahoo.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/01/2003 09:49 | | | AM | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: DATETIME query [1966] | >------------------------------------------------------------------------------- --------------------------------------------------------------| Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. I am trying to set a constant in a select statement based on the hour of a datetime column, but I can't seem to find the correct syntax to test the 'hour'. Here is my sql: select via_whse_nbr, via_ord_sls_pgm, via_ord_dlr_type, via_trans_dte_time, CASE WHEN ( (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME (via_trans_dte_time) HOUR TO HOUR < 04 ) THEN 'BATCH' ELSE ' IMM' ) END, via_ord_tot_items from viaware_trans where via_trans_code = 'OMC' and via_trans_date > '09-01-2003' and via_whse_nbr = '15' Any ideas welcome. TIA. Kevin Struckhoff Yamaha Motors U.S. kevin_struckhoff@yamaha-motor.com

↪ replying to KEVIN STRUCKHOFF

ART KAGEL, BLOOMBERG/ 65E 55TH — Thu, 02 Oct 2003 12:46:53 — source: IIUG Forums & Mailing Lists

In addition to the logic error of using AND rather than OR for the disjoint segments, as Jonathan already pointed out, you have to compare apples to apples so either, as Jonathan points out, you have to convert the 4 & 20 to DATETIME or convert the hours to integers. Here's the latter: CASE WHEN ( HOUR(via_trans_dte_time) > 20 HOUR(via_trans_dte_time) < 04 ) THEN 'BATCH' ELSE ' IMM' END, Art S. Kagel ----- Original Message ----- From: Kevin Struc.... <kevinstruckhoff@yahoo.com> At: 10/ 1 13:37 > Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. > > I am trying to set a constant in a select statement based on the hour of a > datetime column, but I can't seem to find the correct syntax to test the 'hour'. > > Here is my sql: > > select > via_whse_nbr, > via_ord_sls_pgm, > via_ord_dlr_type, > via_trans_dte_time, > CASE > WHEN ( > (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME > (via_trans_dte_time) HOUR TO HOUR < 04 ) > THEN 'BATCH' > ELSE ' IMM' > ) > END, > via_ord_tot_items > from viaware_trans > where via_trans_code = 'OMC' > and via_trans_date > '09-01-2003' > and via_whse_nbr = '15' > > Any ideas welcome. > > TIA. > > Kevin Struckhoff > Yamaha Motors U.S. > kevin_struckhoff@yamaha-motor.com

↪ replying to ART KAGEL, BLOOMBERG/ 65E 55TH

ART KAGEL, BLOOMBERG/ 65E 55TH — Thu, 02 Oct 2003 20:52:18 — source: IIUG Forums & Mailing Lists

----- Original Message ----- From: Jonathan Leffler <jleffler@us.ibm.com> At: 10/ 2 14:06 > Dear Art, > HOUR isn't available as standard in my IDS 9.40.UC1 installation - are you > sure you didn't write it? > Also, I fear you omitted the conjunction altogether - the OR is missing... Argg, in a rush to answer and get back to work I extrapolated from the YEAR, MONTH, & DAY functions. You are correct, Jonathan, there is not HOUR() function. OH, and yes I lost the 'OR' somewhere, AH! right here under my coffee! Art S. Kagel > -- > Jonathan Leffler (jleffler@us.ibm.com) > STSM, Informix Database Engineering, IBM Data Management > 4100 Bohannon Drive, Menlo Park, CA 94025 > Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > |---------+----------------------------> > | | "ART KAGEL, ...."| > | | <KAGEL@bloomberg.| > | | net> | > | | Sent by: | > | | forum.subscriber@| > | | iiug.org | > | | | > | | | > | | 10/02/2003 05:46 | > | | AM | > |---------+----------------------------> > > >------------------------------------------------------------------------------- > --------------------------------------------------------------| > | > | > | To: ids@iiug.org > | > | cc: > | > | Subject: Re: DATETIME query [1975] > | > > >------------------------------------------------------------------------------- > --------------------------------------------------------------| > > > > > > In addition to the logic error of using AND rather than OR for the disjoint > segments, as Jonathan already pointed out, you have to compare apples to > apples > so either, as Jonathan points out, you have to convert the 4 & 20 to > DATETIME or > convert the hours to integers. Here's the latter: > > CASE > WHEN ( HOUR(via_trans_dte_time) > 20 HOUR(via_trans_dte_time) < 04 ) > THEN 'BATCH' > ELSE ' IMM' > END, > > Art S. Kagel > > ----- Original Message ----- > From: Kevin Struc.... <kevinstruckhoff@yahoo.com> > At: 10/ 1 13:37 > > > Using INFORMIX-SQL Version 9.21.HC5, IDS 9.30.FC2 on an HP-UX box. > > > > I am trying to set a constant in a select statement based on the hour of > a > > datetime column, but I can't seem to find the correct syntax to test the > 'hour'. > > > > Here is my sql: > > > > select > > via_whse_nbr, > > via_ord_sls_pgm, > > via_ord_dlr_type, > > via_trans_dte_time, > > CASE > > WHEN ( > > (DATETIME (via_trans_dte_time) HOUR TO HOUR > 20 and DATETIME > > (via_trans_dte_time) HOUR TO HOUR < 04 ) > > THEN 'BATCH' > > ELSE ' IMM' > > ) > > END, > > via_ord_tot_items > > from viaware_trans > > where via_trans_code = 'OMC' > > and via_trans_date > '09-01-2003' > > and via_whse_nbr = '15' > > > > Any ideas welcome. > > > > TIA. > > > > Kevin Struckhoff > > Yamaha Motors U.S. > > kevin_struckhoff@yamaha-motor.com

Glossary

IDS (Informix Dynamic Server)