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     [SOLVED] Date formats from SQL not date in Excel

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Date formats from SQL not date in Excel
 Login/Join
 
DWaybright
Platinum Member
posted
I have a fex that runs SQL to pull some data and creates a spreadsheet. Fields that are defined as DATE type on the SQL table are showing up as GENERAL type in Excel and the users can't work with them as dates. Changing the column format in Excel didn't fix the issue.
How can I get the columns to be dates in Excel?
Thanks!

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


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Waz
Expert
posted Hide Post
What format are the dates coming out of the query ?

Are you holding the extract ?

USE ?FF {HOLD Name}


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
DWaybright
Platinum Member
posted Hide Post
Waz, you always know how to get to the root of it! Big Grin
My dates are in the SQLOUT Hold file as A10V. Can I convert them to a date field with something like datefield/MDYY=SQLDateField; ?


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Waz
Expert
posted Hide Post
You have two options.

1: change the format of the date in the SQL to get a date in the hold file.

2: comvert the A10 to a date. This will take a couple of steps.

The silly season third option is to rewrite the master file to read the A10 as YYMD or what ever format date is stored.


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
DWaybright
Platinum Member
posted Hide Post
The field is defined as "DATE" type in the SQL table. I'm running SQL code to put the data in a HOLD file for the report (ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_Reporting
SQL SQLMSS PREPARE SQLOUT FOR
SELECT...). I don't know why it would put the date field as type A10V in the HOLD file, but I need it in a date format for the Excel output of the report. I've tried a few convert functions, but I haven't found the right one. If you can point me in the right direction, I (and my users) will be eternally grateful!


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Wep5622
Virtuoso
posted Hide Post
What type of HOLD-file? The default is format ALPHA, which could explain the involuntary conversion.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
DWaybright
Platinum Member
posted Hide Post
Hmmm... I don't really know. I just use that SQL SQLMSS PREPARE statement and then TABLE FILE SQLOUT to generate the report.


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Wep5622
Virtuoso
posted Hide Post
Not likely to be the cause then.

For a function, something like this does the trick:
DEFINE FILE CAR
	DATESTR/A10 = '2018/12/21';
END
TABLE FILE CAR
PRINT COMPUTE DATE/YYMD = DATECVT(EDIT(DATESTR, '9999$99$99'), 'A8YYMD', 'YYMD');
BY COUNTRY
IF RECORDLIMIT EQ 1;
END


Note that EDIT() usually doesn't translate to SQL cleanly, so you probably don't want it in a DEFINE.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
DWaybright
Platinum Member
posted Hide Post
Wep,
Thanks for the hint. I had to convert the date to an I8 field (CAST(replace(Convert(varchar(10), inv.Period_Effective_Date, 103),'/','') AS INT) as PED) in my SQL then do a Define to get the DATECVT to work ( d_date/MDYY=DATECVT(PED,'I8DMYY','MDYY') ). For whatever reason, WF didn't like the compute (no error, but no data either).

Thanks everyone!


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 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     [SOLVED] Date formats from SQL not date in Excel

Copyright © 1996-2020 Information Builders