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] Extract date from timestamp
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Extract date from timestamp
 Login/Join
 
Platinum Member
posted
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,


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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,


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 62 | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Ronibi,

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


Waz...

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

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
  
-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


WebFOCUS 8206, Unix, Windows
 
Posts: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Waz...

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

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hai All

it will take me days/weeks/months/years to read that date manual Smiler . 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


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1847 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
All thnx for the help.

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

Case closed.

Regards Ron


WebFOCUS AppStudio 8.2.04
WebFocus Datamanagement Console 8.1M
DMC
 
Posts: 115 | Registered: August 29, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Extract date from timestamp

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