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,

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)

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.

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

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,

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

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,

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.

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

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.

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.

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.

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.

cburtt, thank you, I do like this idea very much.

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.

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

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, 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)

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.

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.

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.

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...

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.