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,

What format are the dates coming out of the query ?

Are you holding the extract ?

USE ?FF {HOLD Name}

Waz, you always know how to get to the root of it!
My dates are in the SQLOUT Hold file as A10V. Can I convert them to a date field with something like datefield/MDYY=SQLDateField; ?

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.

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!

What type of HOLD-file? The default is format ALPHA, which could explain the involuntary conversion.

Hmmm... I don't really know. I just use that SQL SQLMSS PREPARE statement and then TABLE FILE SQLOUT to generate the report.

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.

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!