All,
I want to retrieve the previous month's data. The column that I must use is called DATE_TIME and is in the HYYMDs format. The following script

 -SET &ECHO=ALL;

-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE &DATETMP

-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE &MON_BEG_DT2

-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &MON_END_DT2

-* append time
-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000'; 
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';

-TYPE &FR_DT
-TYPE &TO_DT


TABLE FILE MY_FILE
PRINT
     USER_ID
     DATE_TIME
     BYTES_SENT
     BYTES_RECEIVED
BY USER_ID

WHERE DATE_TIME FROM DT(&FR_DT) TO DT(&TO_DT);

WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END
 

results the following error message.

 -* store the last month's current date in &DATETMP parameter
 -SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
 -TYPE 20101106
 20101106
 -* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
 -SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
 -TYPE 20101101
 20101101
 -* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
 -SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
 -TYPE 20101130
 20101130
 -SET &FR_DT = EDIT(20101101,'99999999') || '00.00.00.000000';
 -SET &TO_DT = EDIT(20101130,'99999999') || '23.59.59.999999';
 -TYPE 2010110100.00.00.000000
 2010110100.00.00.000000
 -TYPE 2010113023.59.59.999999
 2010113023.59.59.999999
 TABLE FILE MY_FILE
 PRINT
 USER_ID
 DATE_TIME
 BYTES_SENT
 BYTES_RECEIVED
 BY USER_ID
 WHERE DATE_TIME FROM DT(2010110100.00.00.000000) TO DT(2010113023.59.59.999999);
 WHERE READLIMIT EQ 10
 ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
 END
 0 ERROR AT OR NEAR LINE     33  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC36346)  INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
 (FOC009) INCOMPLETE REQUEST STATEMENT
 BYPASSING TO END OF COMMAND
 

Here, I tried to use the DT function to change the format of the date, but was not successful. I would prefer to have the format changed in the Dialogue Manager. What is the best way to change the format of &FR_DT and &TO_DT to 'HYYMDS' to successfully compare them against DATE_TIME column?

Thank you for your help,

Seyed

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

Make sure that there is a space between the date and time, and a colon between the hours and minutes, and minutes and seconds.

-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') | ' 00:00:00.000000'; 
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') | ' 23:59:59.999999';

Waz,
I appreciate you help with this. I followed your advice, but now I am getting the following error message:

  
-* store the last month's current date in &DATETMP parameter
 -SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
 -TYPE 20101106
 20101106
 -* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
 -SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
 -TYPE 20101101
 20101101
 -* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
 -SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
 -TYPE 20101130
 20101130
 -* append time
 -*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
 -*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
 -SET &FR_DT = EDIT(20101101,'99999999') | ' 00:00:00.000000';
 -SET &TO_DT = EDIT(20101130,'99999999') | ' 23:59:59.999999';
 -TYPE 20101101 00:00:00.000000
 20101101 00:00:00.000000
 -TYPE 20101130 23:59:59.999999
 20101130 23:59:59.999999
 TABLE FILE MY_TABLE
 PRINT
 USER_ID
 DATE_TIME
 BYTES_SENT
 BYTES_RECEIVED
 BY USER_ID
 WHERE DATE_TIME FROM DT(20101101 00:00:00.000000) TO DT(20101130 23:59:59.999999);
 WHERE READLIMIT EQ 10
 ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
 END
 (FOC1400) SQLCODE IS 242 (HEX: 000000F2) XOPEN: S0003
 :  (242) [S0003] The conversion of a char data type to a datetime data typ
 : e resulted in an out-of-range datetime value.
 (FOC1407) SQL FETCH CURSOR ERROR.  : MY_TABLE

Any ideas what I am doing wrong here?

Thanks again,

Seyed

Have you turned client tracing on to see the SQL generated ?

What is the data source ?

Waz,
Thank you for your help. The table that I am reading is a SQL Server table. Here is the code and the resulting error:

 

-SET &ECHO=ALL;
SET TRACEOFF=ALL
SET TRACEUSER=CLIENT
SET TRACEON=STMTRACE//CLIENT

-* store the last month's current date in &DATETMP parameter
-SET &DATETMP = DATECVT((DATEADD((DATECVT(&YYMD, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
-TYPE &DATETMP

-* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
-SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-TYPE &MON_BEG_DT2

-* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
-SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(&DATETMP,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-TYPE &MON_END_DT2

-* append time
-*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000'; 
-*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';

-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') | ' 00:00:00.000000'; 
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') | ' 23:59:59.999999';




-TYPE &FR_DT
-TYPE &TO_DT


TABLE FILE MY_TABLE
PRINT
     USER_ID
     DATE_TIME
     BYTES_SENT
     BYTES_RECEIVED
BY USER_ID

WHERE DATE_TIME FROM DT(&FR_DT) TO DT(&TO_DT);

WHERE READLIMIT EQ 10
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
END

 

Here is the error:

 
SET TRACEOFF=ALL
 SET TRACEUSER=CLIENT
 SET TRACEON=STMTRACE//CLIENT
 -* store the last month's current date in &DATETMP parameter
 -SET &DATETMP = DATECVT((DATEADD((DATECVT(20101206, 'I8YYMD', 'YYMD')),'M', -1)),'YYMD','I8YYMD');
 -TYPE 20101106
 20101106
 -* use the &DATETMP parameter to build &MON_BEG_DT containing date at the beginning of the month
 -SET &MON_BEG_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
 -TYPE 20101101
 20101101
 -* use the &DATETMP parameter to build &MON_END_DT containing date at the end of the month
 -SET &MON_END_DT2=DATECVT((DATEMOV((DATECVT(20101106,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
 -TYPE 20101130
 20101130
 -* append time
 -*-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
 -*-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';
 -SET &FR_DT = EDIT(20101101,'99999999') | ' 00:00:00.000000';
 -SET &TO_DT = EDIT(20101130,'99999999') | ' 23:59:59.999999';
 -TYPE 20101101 00:00:00.000000
 20101101 00:00:00.000000
 -TYPE 20101130 23:59:59.999999
 20101130 23:59:59.999999
 TABLE FILE LOG_DETAILS
 PRINT
 USER_ID
 DATE_TIME
 BYTES_SENT
 BYTES_RECEIVED
 BY USER_ID
 WHERE DATE_TIME FROM DT(20101101 00:00:00.000000) TO DT(20101130 23:59:59.999999);
 WHERE READLIMIT EQ 10
 ON TABLE COLUMN-TOTAL AS 'TOTAL' 'BYTES_RECEIVED' 'BYTES_SENT'
 END
 16.28.59 AE    SELECT  TOP 10 T1."DATE_TIME",T1."USER_ID",T1."BYTES_SENT",
 16.28.59 AE   T1."BYTES_RECEIVED" FROM wslogdb70.dbo.log_details T1 WHERE
 16.28.59 AE   (T1."DATE_TIME" BETWEEN '16777216 00:00:00.000' AND '16777216
 16.28.59 AE   00:00:00.000') ORDER BY T1."USER_ID";
 (FOC1400) SQLCODE IS 242 (HEX: 000000F2) XOPEN: S0003
 :  (242) [S0003] The conversion of a char data type to a datetime data typ
 : e resulted in an out-of-range datetime value.
 (FOC1407) SQL FETCH CURSOR ERROR.  : MY_TABLE

 

Thank you,

Seyed

Well thats bizarre, the date is converted to a number.

Can you try adding the "/" chars to the date and see what happens.

I would try two things. First, try using .EVAL to make sure your DM variables are being properly evaluated:

WHERE DATE_TIME FROM DT(&FR_DT.EVAL) TO DT(&TO_DT.EVAL);

Second, try placing your date values directly in the WHERE statement without the EDITs:

WHERE DATE_TIME FROM DT(&MON_BEG_DT2 00:00:00.000000) TO DT(&MON_END_DT2 23:59:59.999999);

My preference is the second approach.

I have to admit, I haven't had this sort of issue.

I've been useing

WHERE MODIFY_DTM LE DT(&SELDATE001..&SELTIME001)

without any issues.

Could it also be something to do with the version of MSSQL ?

quote:

HYYMDs format

The 's' means hour:minute:second.millisecond

23:59:59.999999 is to microsecond

try 23:59:59.999

JG,
Your suggestion resolved the error. I changed

  
-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') || '00.00.00.000000';
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') || '23.59.59.999999';

to

-SET &FR_DT = EDIT(&MON_BEG_DT2,'99999999') | ' 00:00:00.000';
-SET &TO_DT = EDIT(&MON_END_DT2,'99999999') | ' 23:59:59.999'; 

and that fixed the error. I just have one more question though, why hour, minute and second are separated by a ':' but millisecond is separated by a '.'?

Thank you very much,

Seyed

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

I guess it is because of the different meaning of the parts. When you separate hours from minutes, (15:59) you do not want to be able to misinterpret it (15.59, which may mean something else entirely). Same for separating minutes and seconds. But seconds and milliseconds can't be misinterpreted, so there you can use the . for separation.

GamP,
Thank you for your explanation. It now makes sense to me.

Seyed