[SOLVED] date/time field error

January 03, 2013, 04:14 PM

I have coded a routine fex to capture water main breaks/leaks which occurred between midnight and noon today.

I created a date/time field in a define which I want to compare against the RECDATE field (A8YYMD) in the master file. I am having a problem with the statement:

quote:

WHERE RECDTE FROM DATEH1 TO DATEH2 ;

as I get an error message:

quote:

COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED

Can anyone help me?

Thanks.

This message has been edited. Last edited by: Tomsweb,

January 03, 2013, 04:41 PM

Prarie

what is the format of DATEH1 and DATEH2?

January 03, 2013, 04:45 PM

Tomsweb

Here is my code:

  
-SET &ECHO=ALL;                

DEFINE FILE MMDB1        
TDATE/YYMD WITH WO = &YYMD; 
IDATE/I8YYMD = EDIT(RECDTE); 
DATEA/A40 = 'JANUARY 03 2013 12:00:00AM';  DATEB/A40 = 'JANUARY 03 2013 11:59:59AM'; 
DATEH1/HDMYYS = HINPUT(40,DATEA,8,'HDMYYS');  
DATEH2/HDMYYS = HINPUT(40,DATEB,8,'HDMYYS');
WORKTYPE/A12 = DECODE WDC ('211' 'BREAKS' '212' 'BREAKS' '213' 'BREAKS'
 '325' 'LEAKS' '328' 'LEAKS' ELSE 'NONE'); 
END 

TABLE FILE MMDB1
COUNT WO 
BY WORKTYPE  
WHERE STATUS NE 'X'; 
WHERE WDC EQ '211' OR '212' OR '213' OR '325' OR '328' ; 
WHERE RECDTE FROM DATEH1 TO DATEH2; 
IF RECORDLIMIT EQ 10 
END 
-RUN

This message has been edited. Last edited by: Tomsweb,

January 03, 2013, 05:12 PM

Prarie

quote:
A8YYMD

All the dates for comparison are going to have to be the same format

January 03, 2013, 05:26 PM

Tomsweb

Can I maintain A8YYMD w the time stamp?

January 04, 2013, 08:21 AM

RSquared

We use the DT function.

/*
WHERE RECDTE GE DT(&Today2 00:00:00.000) AND RECDTE LE DT(&Today2 11:59:59.999);
\*

January 04, 2013, 09:25 AM

jgelona

First you are getting the error because RECDTE is not a datetime field.

Second, why would you want to compare a plain date field with a specific time range? You need a matching time for RECDTE to make a valid comparison. I say that because if you convert RECDTE to a datetime field, the time component will be midnight (00:00:00.000) and every RECDTE for a given day will pass.

If you have a time component (i.e. RECTIME) there are various ways to take RECDTE and RECTIME and make one field you can use in your WHERE statement.

January 04, 2013, 10:11 AM

Tomsweb

I need to ask if there is a time component field in the databasse.

January 04, 2013, 04:43 PM

Tomsweb

I have learned that there are two fields in the master file which
track the time when orders are entered into the system. These fields
are RECTIME/A4 (ie., 1200 for noon), and RECAP (A for AM; P for PM).

Since you must concatenate these fields to distinguish 4am from 4pm, I thought
that I would convert all times greater than 12 to military time. I have done so in
this code, but the WHERE statement is just ignored when I run this fex.

 
DEFINE FILE MMDB1                                             
TDATE/YYMD = &YYMD;                                           
IDATE/I8YYMD = EDIT(RECDTE);                                  
DYMD/YMD = IDATE;                                             
DYYMD/YYMD = DYMD;                                            
AGEDIF/YYMD = DATEADD(TDATE, 'D', -30);                       
RCTM/A5 = RECTIME | RECAP;                                    
HRI/I4 = EDIT(RECTIME);                                       
MRECTM/I4 = IF RECAP EQ 'P' THEN (HRI + 1200) ELSE HRI;       
ARECTM/A4 = EDIT(MRECTM);                                     
NDTTM/A12 = RECDTE | ARECTM;                                  
NHDATE/HYYMDS = HINPUT(12,NDTTM,8,'HYYMDS');                  
END                                                           
                                                              
TABLE FILE MMDB1                                              
PRINT                                                         
RECDTE                                                        
RECTIME                                                       
NHDATE   
BY ASSIGNEE                                     
WHERE DYYMD GE AGEDIF;
WHERE NHDATE LE DT(2013/01/03 11:59:59) ; 
-*WHERE NHDATE GE DT(2013-01-01 00:00:00)       
-*  AND NHDATE LE DT(2013-01-01 11:59:59) ;     
IF RECORDLIMIT EQ 15                            
-* TABLE SAVE                                   
END                                             
-RUN

This message has been edited. Last edited by: Tomsweb,

January 07, 2013, 01:53 PM

jgelona

The first thing I see wrong is the computation for MRECTM. It should be:

MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE HRI;

The reason is you do not want to add 12 hours to 12:00 pm (noon) through 12:59 pm. You will get an invalid time.

Second I suggest you remove the where clauses and just print 10-20 rows to make sure your conversions are working correctly.

January 07, 2013, 04:11 PM

Tomsweb

quote:
I have made the changes you suggested and tested it. The only problem I see here
is I will have 12:12 at 12 minutes after midnight and 12:12 at 12 minutes after noon.

However, I do get 13:12 in the afternoon and a 1:12 in the early morning.

quote:
The first thing I see wrong is the computation for MRECTM. It should be:

MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE HRI;

The reason is you do not want to add 12 hours to 12:00 pm (noon) through 12:59 pm. You will get an invalid time.

January 08, 2013, 09:14 AM

jgelona

Well if I understand you, you are saying that you could have RECTIME with a value of 1212 and RECAP could have a value of 'A' which you would want to become 00:12 am. Then your code would look like this:

MRECTM/I4=IF RECAP EQ 'P' AND HRI LT 1200 THEN HRI+1200 ELSE
          IF RECAP EQ 'A' AND HRI GE 1200 THEN HRI-1200 ELSE HRI;

RECTIME with values of 0100 through 1159 and RECAP with a value of 'P' need 12 hours added.
RECTIME with values of 1200 through 1259 and RECAP with a value of 'A' need to have 12 hours substracted;
All of values of RECTIME and RECAP need no adjustment. These are:
RECTIME with values of 0100 through 1159 with RECAP being 'A' and RECTIME with values of 1200 through 1259 with RECAP being 'P'