David, AHHA!
>>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!!!! Your FORMAT and SEQUENCE ARE NOT compatible. Format has no seconds and sequence does. Try changing SEQ to HHMM and see if things change! Also, consider that if the database row has been saved with a seconds value, it may never be found with other settings. Some testing is in order to find out the exact reason for the failure. Try displaying the database rows with TIME FOR HH:MM:SS AP and see what they really contain. --Dennis McGrath -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of David M. Blocker Sent: Saturday, November 30, 2002 8:13 PM To: [EMAIL PROTECTED] Subject: Re: Time format/Sequence challenge? 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/ ================================================ 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/
