Hello

I have a timestamp in DB2, lets call it FIELD_A.
Example 2015-02-04-21.32.15.929470. This field can also contain NULL value (equal to MISSING in WebFOCUS)
I want to have the date portion of this field, so 2015-02-04. How do I do that?
When I use the DB_EXPR function in the DEFINE option of InfoAssist, it says WITH OPTION IS REQUIRED. But I don't want to change my business view (cluster join), I want my user to be able to embed this WITH OPTION. How does he do that?

We also have a variable -SET &SSBI_TODAY = &YYMD;
In Info Assist I want to build a filter where
FIELD_A is MISSING or datepart(FIELD_A) < &SSBI_TODAY

With regards
Ron

This message has been edited. Last edited by: FP Mod Chuck,

Create a DEFINE such as

FIELD_A_dt /YYMD = FIELD_A;

should do the trick.

Then

WHERE FIELD_A_dt EQ MISSING OR FIELD_A_dt LT &SSBI_TODAY;

In InfoAssist in the ribbon I choose Data and then Detail(Define).
The Define box pops up.
In Field I fill in FIELD_A_dt, in Format YYMD, in the lower box FIELD_A (the timestamp).

Clicking on OK i get an error.
(FOC282) result of expression is not compatible with the format of the field: FIELD_A_dt

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

This is all a question a field format.
Depending on the format of FIELD_A you may have to use one or more date function to have what you need.
But you have to figure out which function(s) you will need to pass from one format to another and there is a lot of documentation and sample regarding this.
Only for date conversion and function, there is a whole book on the subject.

Some functions:
Date Functions

Hi Ron

As Martin says - it's all about what format your Master file has for the DATE field in the first place. We'd need to know that first.

In WebFOCUS, you'll hear us talk about 3 different kinds of dates. You can use any of them. Here's a short list:

1) 'old dates' - In the old days, we might say a date was an I8YYMD. More of an Integer 8 characters long and FOCUS should format it to look like YYMD as far as adding some slashes go.

2) New Dates - around the time of the year 2000 worries, we made a 'new date' that just a format like YYMD. like other databases, this one is really stored as a number of days since the beginning of time and then when it's displayed, we format it to look like YYMD. This lets us do math or comparisons more easily.

3) Date-Timestamps - these were made to support the very common SQL RDBMS kind of date where a timestamp is also in the date.

My guess is that you created your master file and got a FORMAT=DATE out of it. So what you need to do is change this to just a 'new date' like YYMD. But you have to find the right function to help you do this.

Martin gave you the list of all those date functions but I think you might need these Date-Time Functions

Especially take a look at HDATE and see if that helps.

Dates seem to take a lot of fiddling around with sometimes. Check you SQL to see if the WHERE test you'd like to add really gets passed of in the SQL statement. Usually you can get it to pass off, but it may not work right off the bat.

Start with HDATE. Let us know what the USAGE= and ACTUAL= say for the formats in your master.

Later!
Toby

Hi Ronibi,

Can you seu the date time stamp conversion function HDATE ?, it will extract the date portiuon of a datetime field (formats H????)

  
-SET &SSBI_TODAY = &YYMD;

DEFINE FILE ORDERS
 TODAY_DT/YYMD=&YYMD;
 NEW_ORDER_DT/YYMD=HDATE(ORDERS.ORDERS.ORDERDATE , 'YYMD') ;
END
TABLE FILE ORDERS
BY TODAY_DT
BY NEW_ORDER_DT
BY CUSTOMERID
WHERE ORDERS.ORDERS.ORDERDATE IS MISSING OR NEW_ORDER_DT LT &SSBI_TODAY.(|FORMAT=YYMD).Date.QUOTEDSTRING;
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
ENDSTYLE
END

Hi Ronibi,

I hope that you don't take the examples verbatim, and instead research the documentation of WebFOCUS functions to get a better understanding on whats available.

Hai All

it will take me days/weeks/months/years to read that date manual . Nevertheless I will have a look at it.

To answer the question: Usage and actual of the field is HYYMDm. And MISSING is ON.

Regards
Ron

In the example I posted above, ORDER_DATE is an HYYMDS field. This sample does what you're looking to accomplish and it can be created in InfoAssist.

All thnx for the help.

The solution from BabkNYC works. And thnx for the reference to the functions manual.

Case closed.

Regards Ron