Focal Point
SQL Server date/time fields
May 19, 2008, 05:21 PM
Jason K.SQL Server date/time fields
I would like to add a where clause to my report similar to
sqlserverDate_Field GE TIME_RIGHT_NOW - 24 hours
My usual method is to use &YYMD - 1, and this works for our AS400 as the dates are stored as numbers anyhow.
I'm sure this is easy for you fellas. Thank you for your patience.
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
May 20, 2008, 08:56 AM
PBrightwellIt is basically the same logic as you are doing:
-SET &CMPDT=AYMD(&YYMD,-1,'I8YYMD');
-SET &CMPDTTM=EDIT('&CMPDT.EVAL','9999/99/99')|' '| &TOD;
I haven't tested this, you may have to adjust the syntax.
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
May 20, 2008, 09:03 AM
Francis MarianiThis is what I do to manipulate date-time values for SQL selection criteria:
-SET &CUR_DTTM = &DATEYYMD | ' 00:00:00';
-SET &NEW_DTTM = HCNVRT(HADD(HINPUT(14, '&CUR_DTTM.EVAL' , 14, 'HYYMDS'), 'HOUR', -24, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20');
-TYPE &CUR_DTTM &NEW_DTTM
To use the Date-Time functions in Dialogue Manager, you need to convert the string to a Date-Time field and then convert it back to a string after the function is processed.
It looks daunting, but if you look at each part of the DM line of code, it's easy:
HINPUT(inputlength, 'inputstring', length, outfield) - Converts an Alphanumeric String to a Date-Time Value
HADD(value, 'component', increment, length, outfield) - Increments a Date-Time Value
HCNVRT(value, '(fmt)', length, outfield) - Converts a Date-Time Value to Alphanumeric Format
...
WHERE DATE_TIME_COLUMN GE '&NEW_DTTM'
...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 20, 2008, 09:40 AM
Jason K.thanks!
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
May 28, 2008, 09:46 AM
Jason K.Francis,
I'm using the -set's that you mentioned, but we're getting an error on this line..
WHERE DELIVERED GE '&NEW_DTTM';
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
The field is defined as 'HYYMDI' in the master file, if that makes a difference.
When we do a set echo on, the following shows up in the browser...
WHERE DELIVERED GE '2008/05/27 00:00:00 ';
Is there something I'm missing?
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
May 28, 2008, 09:54 AM
PrarieTry WHERE DELIVERED GE ('&NEW_DTTM');
 | In Focus since 1993. WebFOCUS 7.7.03 Win 2003 |
May 28, 2008, 09:58 AM
Jason K.Prarie,
Thank you for your quick response!
We're getting the same error.
The echo shows
WHERE DELIVERED GE ('2008/05/27 00:00:00 ');
It looks like a valid date to me.
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
May 28, 2008, 10:14 AM
Francis MarianiYou MAY need to append '.000' for milliseconds to the end of the date-time, eg. '2008/05/27 00:00:00.000'.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 28, 2008, 10:25 AM
Francis MarianiThe WHERE statement:
WHERE DT1 GT DT('&NEW_DTTM1');
(Forget about the .000).
-SET &ECHO=ALL;
-SET &CUR_DTTM = &DATEYYMD | ' 00:00:00';
-SET &NEW_DTTM1 = HCNVRT(HADD(HINPUT(14, '&CUR_DTTM.EVAL' , 14, 'HYYMDS'), 'HOUR', -24, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20') ;
-SET &NEW_DTTM2 = HCNVRT(HADD(HINPUT(14, '&CUR_DTTM.EVAL' , 14, 'HYYMDS'), 'HOUR', -22, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20') ;
-SET &NEW_DTTM3 = HCNVRT(HADD(HINPUT(14, '&CUR_DTTM.EVAL' , 14, 'HYYMDS'), 'HOUR', -20, 8, 'HYYMDS'), '(HYYMDS)', 20, 'A20') ;
DEFINE FILE CAR
DT1/HYYMDIA = IF COUNTRY EQ 'ENGLAND' THEN DT(&NEW_DTTM1) ELSE IF COUNTRY EQ 'JAPAN' THEN DT(&NEW_DTTM2) ELSE DT(&NEW_DTTM3);
END
TABLE FILE CAR
PRINT
COUNTRY
DT1
WHERE DT1 GT DT('&NEW_DTTM1');
ON TABLE HOLD
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 28, 2008, 10:40 AM
Jason K.Good work on the DT() Francis.
Thanks again!
Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
May 28, 2008, 10:42 AM
PrarieThanks Francis...that's what I meant...forgot my DT.
| In Focus since 1993. WebFOCUS 7.7.03 Win 2003 |
May 28, 2008, 04:45 PM
TravBelieve it or not, this is actually a good example of where the report painter does a good job. If you run it through that, it should put the DT() in there for you. Also, for what it's worth, in my experience, the DT() function only works with static (amper) values -- NOT with columns/defines... I believe it will actually throw an error if you try anything else.
Glad you got it working though.
Production: 7.6.6 WF Server <=> 7.6.6 WF Client <=> 7.6.6 Dev Studio
Testing: <none>
Using MRE & BID. Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
