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     Microsoft SQL Server Date-Time format - Best practice question
Go
New
Search
Notify
Tools
Reply
  
Microsoft SQL Server Date-Time format - Best practice question
 Login/Join
 
Expert
posted
Microsoft SQL Server does not have a Date-only data-type, just a Date-Time data-type. When you generate a WebFOCUS Master for a table with Date-Time data-types, they are generated as HYYMDs formatted columns. You can modify the Master and change the format to DATE, YYMD.

I don't like modifying my Masters after they're generated, so I add a DEFINE in a fex to convert the HYYMDs column to YYMD with this:

EXPENSEDATE_YYMD/YYMD = HDATE(EXPENSEDATE, 'YYMD');

What do other people do?

Cheers,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I modify the master and if I need the time also I add an extra field in the master that points to the same database filed with an other format (HYYMDs)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
We have
SQL SQLMSS SET DATETIME OFF
in the edasprof.prf
In case we want to create metadata with datetime for some data source we temporarily comment it out.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
Francis,

Gone are the days of losing time consuming edits of the master files when regenerating them. Most, if not all, the modifications are retained.

For Date fields I think the most useful method is the additonal column in the master, and if your masters are changing that often then you have to ask the question why?

Just take advantage of the fact that the ALIAS must conform to the host tables real column name and add additional columns -
FIELDNAME=VOUCHER_DATEH, ALIAS=VOUCH_DATE, USAGE=HYYMDs, ACTUAL=HYYMDs, $
FIELDNAME=VOUCHER_DATE, ALIAS=VOUCH_DATE, USAGE=DMYY, ACTUAL=DATE, $

Easy and useful just as Frank suggests!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Thanks for the comments.

Tony, what do you mean by "Gone are the days of losing time consuming edits when regenerating them"?

If I do as you suggest and add an additional column, when I regenerate the master file, won't the additional column get wiped out?

I find the problem with using SQL SQLMSS SET DATETIME OFF is that if you have a regular Date field and a Date-Time field (a row update time-stamp for example), you can't mix the two - it's either all Date or all Date-Time.

Thanks,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Francis,

Very probably. The edits to which I was refering were the description and title fields that everyone wanted to reduce the need to add
AS 'some nice title'
in the fex. In versions gone by these were lost on regenerating the mas, not now though.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Tony, is this in the post-5.3.2 world? As far as I can tell, the Master and Access file get completely replaced by the regenerated Master.

Cheers,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
I think this was a new option post 5.2 at least. In 7.1.3 we get the option to "replace" the master file or just "refresh" it. If we "refresh" it the previous changes made to it are not over-written. On the down side, though, if the field's data length has changed, that doesn't get updated either.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Master
posted Hide Post
I think in 5.3 refresh or replace recreates the MFD. Try creating an include with your frequently modified fields as a
DEFINE FILE tablename ADD


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
This is my preferred method - using DEFINE FILE.

I didn't realize that "Refresh" did not overwrite the previous changes - so what does it refresh?

What I don't like about "Refresh" is that the generated Master is different than the "Replace" one:

Replace:

FILE=BASEL_ACCOUNT_STATUS_C, SUFFIX=DB2     ,$                                  
SEGNAME=BASEL_ACCOUNT_STATUS_C,                                                 
SEGTYPE=S0 ,$                                                                   
FIELD=ACCOUNT_STATUS_CD                   ,ACCOUNT_STATUS_CD,                   
A1          ,A1          ,MISSING=OFF,$                                         
FIELD=ACCOUNT_STATUS_DS                   ,ACCOUNT_STATUS_DS,                   
A35         ,A35         ,MISSING=OFF,$                                         

Refresh:

FILENAME=BASEL_ACCOUNT_STATUS_C, SUFFIX=DB2     , $
  SEGMENT=BASEL_ACCOUNT_STATUS_C, SEGTYPE=S0, $
    FIELDNAME=ACCOUNT_STATUS_CD, ALIAS=ACCOUNT_STATUS_CD, USAGE=A1, ACTUAL=A1, $
    FIELDNAME=ACCOUNT_STATUS_DS, ALIAS=ACCOUNT_STATUS_DS, USAGE=A35, ACTUAL=A35, $


It would be nice if the two methods of creating as Master produced consistent results.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Well, the "Refresh" is interesting:

I changed the size of the ACCOUNT_STATUS_DS column to USAGE=A10, ACTUAL=A10 and Refresh changed it to USAGE=A10, ACTUAL=A35.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Have you considered creating a MS/SQL view of the table in which the view makes the format conversion from MS/DateTime format to an 8-character yyyymmdd string that is compatable with FOCUS's date formats?

TABLE FILE this view instead of the underlying MS/SQL table.

This has three advantages:
1) The date conversions are always there. You don't have to include *.fex code that does it in every FOCUS process using the table's dates.
2) It's likely quicker in elapsed time to do it on the SQL server than on the WF server.
3) If you need to WHEN on the date, the selection takes place on the SQL server and rows that do not meet the WHEN criteria are not sent over the network to the WF server. FOCUS is sent only the rows that it needs.


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
i put a function in my edasprof
EXPENSEDATE_YYMD/YYMD =F_DATE(EXPENSEDATE);
which translates the datetime to a smart.
This way, my end user of reporting objects on those sql files can define a field with no ageda.
This way i can also provide stuff like
F_MONTH
or
F_WEEK
and keep from having to remember the syntax myownself.
I try to convince dba's to store a key date in integer, then all this ageda goes away, but alas, that would be too easy.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
cburtt, thank you, I do like this idea very much.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Master
posted Hide Post
I found this helpful.

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

I used to use the Oracle Trunc() function for what you're talking about.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
My opinion:

1. View.
Drawback when datetime field is indexed, index is not used.

2. DEFINE-field.
If you WHERE or BY : no good.
WebFOCUS will try get all table and then filtering/grouping without SQLServer.

3. Additional column in MFD.
I've made experiment - it's excellent!
FOCUS-WHERE translated in MSSQL-WHERE,
FOCUS-BY translated in GROUP BY.

And "Refresh synonym" does not delete this column.

My opinion:
I think additional column is best idea.

PS.
Another option: MSSQL computed indexed field.
But additional column - is better solution

Best regards


WF 7.6.2, WinNT/2K, MSSQL 2K, MSOLAP 2K, BID
 
Posts: 79 | Location: Moscow | Registered: April 27, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
ageda


Wot's dat?

I've thought about cburtt's idea and unfortunately it also requires a DEFINE to convert the extra view column from char to date.

An extra column in he Master may be the best way to go.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
Francis, an idea to do efficient query's on datetimestamps is to use DT:

-SET &DATEFROM = '31/05/2007'
-SET &DATETO = '01/01/2007'
...
WHERE DATEFIELD GE DT(&DATEFROM)
WHERE DATEFIELD LE DT(&DATETO 23:59:59.999)


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 453 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
Hi,

In our case we never report on the sql tables directly. This is because we are limited with the name of fields that complicated to be undestood by our users. And also because of some data type fields.
In most of the cases we create views that allow us to rename fields as we want and also to change their type.

Majid.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Jason K, thanks for pointing me to that link - interesting stuff.

Frans, this is what I normally do.

Majid, that's a good idea.

Here's something no one pointed out and is exactly the reason why I don't muck around with a Master generated by WebFOCUS.

For ease of use, a programmer where I work changed the format of two DateTime fields in the master for a SQL Server table from HYYMDs to YYMD. This is great for reporting, but if the DateTime field in the table contains Time, then a straight-forward filter on the Date will not work, i.e WHERE MODIFIEDON EQ '2007/12/05' returns zero rows, while WHERE MODIFIEDON GE '2007/12/05' AND MODIFIEDON LT '2007/12/06' RETURNS THE ROWS I'm expecting.

An unsuspecting soul would look at the Master and only see YYMD and not understand why a filter would not work.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
This occurs for the data extraction step where SQL is generated. The dates are treated as normal Date fields for any post-data extraction steps using the HOLD files.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Guru
posted Hide Post
just to throw it out there...
I do a lot of native sql in my fexes, so when I need a date, I just do this:
ENGINE SQLODBC SET DEFAULT_CONNECTION CONN_NAME
SQL SQLODBC
SELECT CONVERT(CHAR(10),DATE_FIELD,111)
FROM TABLE_NAME;


But then you probably should just create a view...


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
The problem with creating a view that extracts the date portion of a DateTime column is that the substring is char - you still need a DEFINE to turn it into a Date so you can perform date functions on it.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Microsoft SQL Server Date-Time format - Best practice question

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