Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Creating a timestamp [Resolved]

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Creating a timestamp [Resolved]
 Login/Join
 
Ted Michalski
Platinum Member
posted
I can get the first and last day of the current month with this code:

DEFINE FILE DATE
CURR_DATE/YYMD ='&YYMD';
BEG_CUR_MO/MDYY=DATEMOV(CURR_DATE,'BOM');
END_CUR_MO/MDYY=DATEMOV(CURR_DATE,'EOM');
END

My question is, how fo I convert this date MM/DD/YYYY
to a timestamp in this format
YYYY-MM-DD HH:MM:SS:NNN

This message has been edited. Last edited by: Ted Michalski,


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report This Post
N.Selph
Guru
posted Hide Post
Look up the HDTTM function.


(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, 2003Report This Post
Tom Flynn
Expert
posted Hide Post
I'm sure there are better ways, but, here's one:

  
DEFINE FILE CAR
  DUMMY/A1 WITH CAR = ' ';
  DT_CUR1/HYYMDm = HGETC(8, 'HYYMDm');
  DT_CUR2/A26    = HCNVRT(DT_CUR1, '(HYYMDm)', 26, 'A26');
  DT_EOM1/I8YYMD = DATECVT((DATEMOV((DATECVT(&YYMD,'I8YYMD','YYMD')),'EOM')), 'YYMD','I8YYMD');
  DT_EOM2/A23    = EDIT(DT_EOM1,'9999-99-99 ') | EDIT(DT_CUR2,'$$$$$$$$$$$99999999$:999');
END

TABLE FILE CAR
SUM
  DT_CUR1
  DT_CUR2
  DT_EOM1
  DT_EOM2
 BY DUMMY NOPRINT
END
-EXIT


Also, why are you using MDYY when you want YYMD?
And, why a Timestamp on a previous/future date?


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Ted Michalski
Platinum Member
posted Hide Post
I change it to YYMD. We are writing these timestamps to Sql Server to use in a future report.


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report This Post
Waz
Expert
posted Hide Post
As Tom has shown, there are functions specifically for Date-Time stamps, these begin with H.

Check out the documentation.

Here is another way to get the Start and End of the month.

-SET &YM = EDIT(&YYMD,'999999') ;
DEFINE FILE CAR
 BOM_TS/HYYMDs = DT(&YM..01000000000) ;
 NM_TS/HYYMDs  = HADD(BOM_TS, 'month',1,8,NM_TS) ;
 EOM1_TS/HYYMDs = HADD(NM_TS, 'day',-1,8,NM_TS) ;
 EOM2_TS/HYYMDs = HADD(NM_TS, 'millisecond',-1,8,NM_TS) ;
END
TABLE FILE CAR
PRINT BOM_TS
      NM_TS
      EOM1_TS
      EOM2_TS
BY COUNTRY
WHERE RECORDLIMIT EQ 1
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Doug
Expert
posted Hide Post
Keep in mind that a time stamp based on a date will give a time of midnight...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Tom Flynn
Expert
posted Hide Post
Waz,

The example needs '-', not '/', in the date portion for insertion into MSSQL. Also, Doug is correct regarding the time. HGETC gets the actual current time the program is run.

If the actual time is not needed, Ted could just hard-code the time portion to zeroes...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
Wink

Ted

it might be a smart idea for you to buy a nice book called "(Almost) 1001 Ways to Work with DATES in WebFOCUS" ISBN 978-0-9791722-1-2
It cost maybe 25 U$ and is worth all that dollars.


Music




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, 2006Report This Post
Doug
Expert
posted Hide Post
Ditto on the "(Almost) 1001 Ways to Work with DATES in WebFOCUS" note. I have it and will say that it is worth having. Albeit I've not quite used all 1001 things nor do I see the need ... Yet...




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Waz
Expert
posted Hide Post
quote:
The example needs '-', not '/', in the date portion for insertion into MSSQL


All depends on what you use to write the timestamps to SQLServer.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Tom Flynn
Expert
posted Hide Post
Waz,

What part of this do you not understand?

quote:

My question is, how fo I convert this date MM/DD/YYYY
to a timestamp in this format
YYYY-MM-DD HH:MM:SS:NNN


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Waz
Expert
posted Hide Post
I did read the original post, and do understand that the format needed was delimited with dashes, but how is the timestamp going to be loaded into SQL server ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Terri1977
Member
posted Hide Post
Was there ever a solution for this post? I have the same issue. I have YYYY/MM/DD (20180502) and want to change it to 02-MAY-2018


WebFOCUS 8
Windows, All Outputs
 
Posts: 27 | Registered: January 10, 2017Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Creating a timestamp [Resolved]

Copyright © 1996-2020 Information Builders