Dennis, I read your post and did a double take. Wait a minute I thought, isn 't that EXACTLY what I did and it DIDN'T work? So I tried your test as written using my data, and sure enough it correctly FOUND the row. But I KNOW it didn't work yesterday so I dug a little further.
Again, here's the row in the table: VID DateAssign RAssignNo TimeAssign 31 07/10/02 21 11:15 AM If you create the variables with: SET VAR vRAssignNo INTEGER = 21 SET VAR vHVID INTEGER = 31 SET VAR vHDateAssign DATE = 07/10/02 SET VAR vHTimeAssign TIME = 1115 Then: SELECT * FROM RTEHIST WHERE RassignNo = .vRAssignNo AND VID = .vHVID AND DateAssign = .vHDateAssign and TimeAssign = .vHTimeAssign WORKS - it DOES find the row! BUT, my variables were NOT created this way. They were created with a FETCH command from a declare cursor. The time value came from a different row in the same table. I tested it: using the SAME time FORMAT and SEQUENCE settings as above (FORMAT HH:MM AP, SEQUENCE HHMMSS): when the variable is created either from a SELECT INTO command or a FETCH, with EXACTLY the same value, the SELECT fails!!!! OK, Razzak, want to step in here and elucidate? David Blocker ----- Original Message ----- From: "Dennis McGrath" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, November 30, 2002 6:03 PM Subject: RE: Time format/Sequence challenge? > David, > > I created a test to find out: > > SET TIME FOR HHMM AP > SET TIME SEQ HHMM > SET VAR vTime = '1145' > SEL * FROM TESTTIME > 1145 AM > 1145 PM > SEL * FROM TESTTIME WHERE TESTTIME = .vTime > 1145 AM > SEL * FROM TESTTIME WHERE TESTTIME = (.vTime) > -ERROR- Column TESTTIME must be compared to TIME values. > > > This may shed some light on the subject. Seems that when the interpreter > looks at an expression it reads the time variable using the time FORMAT > instead of converting the value to 24 hour time which be unambiguous and > never fail. > > There is a similar issue with dates. With dates I can use the FORMAT() > function to force date values in expressions to four digit years which are > unambiguous. Unfortunately FORMAT() does not work with time values, so one > has to find other solutions. > > In any case, this demo should help point to a workaround. > > --Dennis McGrath > > > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of David M. Blocker > Sent: Saturday, November 30, 2002 1:02 PM > To: [EMAIL PROTECTED] > Subject: Re: Time format/Sequence challenge? > > > Dennis > > Any ideas on the original question, about why the query fails when time > format is set to HH:MM AP? > > David Blocker > > ----- Original Message ----- > From: "Dennis McGrath" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, November 30, 2002 10:25 AM > Subject: RE: Time format/Sequence challenge? > > > > The rsyntax SET TIME point to the docs for the time data type. > > > > I have submitted the boo-boo to RDCC. > > > > Her is the correct syntax: > > > > SET TIME FOR HH:MM AP > > SET TIME SEQ HHMM > > > > No quotes are needed as the interpreter is smart enough to know that the > > TIME FOR setting may need a space. > > > > Now you can enter time as follows: > > 11:45PM will be displayed as 11:45 PM > > 1145PM will be displayed as 11:45 PM > > 1145 will be displayed as 11:45 AM > > 11PM will be displayed as 11:00 PM > > 11AM will be displayed as 11:00 AM > > 11 will be displayed as 11:00 AM > > 1356 will be displayed as 1:56 PM > > > > you could also do: > > SET TIME FOR HHMM AP > > SET TIME SEQ HHMM > > or > > SET TIME FOR HHMMAP > > SET TIME SEQ HHMM > > > > Hope this helps. > > > > --Dennis McGrath > > > > > > > > > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > > Behalf Of Nicky Avery > > Sent: Saturday, November 30, 2002 3:22 AM > > To: [EMAIL PROTECTED] > > Subject: Re: Time format/Sequence challenge? > > > > > > Claudine Robbins wrote: > > > > >Well, it doesn't work anyway. I tried the SET TIME HH:MM AP and it > > >gives an "invalid command" error, > > > > > > > Claudine, > > > > SET TIME is insufficient. It's necessary to specify the SEQ and FOR > > separately and, I believe, to use AP, put the setting param in quotes > > > > RBHelp: "If the format contains spaces or commas, enclose the format in > > quotes." > > > > > > e.g: > > > > SET TIME SEQ 'HHMM AP' > > > > but using the above means that time has also to be entered in quotes > > with a following AM or PM for correct results - a 24-hour clock > > (military time) is much simpler. > > > > SET TIME FOR 'HH:MM AP' > > > > > > David, > > > > Would the quotes make your code work in the desired way? > > > > Nicky > > > > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
