Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Converting Date & Time to HYYMDS
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Converting Date & Time to HYYMDS
 Login/Join
 
Gold member
posted
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,


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Have you turned client tracing on to see the SQL generated ?

What is the data source ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Well thats bizarre, the date is converted to a number.

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


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6104 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
<JG>
posted
quote:
HYYMDs format


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

23:59:59.999999 is to microsecond

try 23:59:59.999
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
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,


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
GamP,
Thank you for your explanation. It now makes sense to me.

Seyed


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Converting Date & Time to HYYMDS

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.