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] Date formats from SQL not date in Excel
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Date formats from SQL not date in Excel
 Login/Join
 
Gold 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 (recent upgrade from 8105M)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 75 | Registered: December 05, 2017Reply With QuoteReport This Post
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.01OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 5930 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold 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 (recent upgrade from 8105M)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 75 | Registered: December 05, 2017Reply With QuoteReport This Post
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.01OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 5930 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Gold 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 (recent upgrade from 8105M)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 75 | Registered: December 05, 2017Reply With QuoteReport This Post
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: 1583 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Gold 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 (recent upgrade from 8105M)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 75 | Registered: December 05, 2017Reply With QuoteReport This Post
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: 1583 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Gold 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 (recent upgrade from 8105M)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 75 | Registered: December 05, 2017Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.