Hello,

I am trying to create a report that would output to CSV format by retaining the exact date format in the report. When I open the report, the date are getting converted to a different format (example: 01/07/2011 is showing as 20110107000000000). I tried reformatting in Excel to date format but it won't change it. I also tried to open in Notepad and the dates are already formatted differently.

This is a file that I need saved as .CSV and needs to be uploaded in a third party website according to the format specified. Below is the code I wrote:

-* File hirerightdva.fex
ENGINE SQLSYB SET DEFAULT_CONNECTION MARTENWARM
SQL SQLSYB
EX mastersys..HiredOrTermedDriversHireRight '&status1','&status2','&status3'
;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END

TABLE FILE SQLOUT
PRINT
userName AS 'Username'
password AS 'Password'
firstName AS 'First'
middleName AS 'Middle'
lastName AS 'Last'
ssn AS 'SSN'
dateOfBirth/HMDYY AS 'DOB'
stateOfLicense AS 'DLState'
licenseNumber AS 'DLNumber'
orderPurpose AS 'OrderPurpose'
hireDate/HMDYY AS 'HireDate'
termDate/HMDYY AS 'TermDate'
subAccount AS 'SubAccount'
referenceAppend AS 'ReferenceAppend'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT COMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


Thanks for any help!

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

Use the FPRINT function.

Simple example:

SOMEDATE/A10=FPRINT( PROC_DT, 'YY-M-D', 'A10' ); 

FPRINT?

Nice to know for future use.

From the WF 7.7.0.2 functions documentation:

"The FPRINT function converts any type of field except for a text field to its alphanumeric equivalent for display. The alphanumeric representation will include any display options that are specified in the format of the original field."

For versions older than v7.7, try the following (assuming your input date is a date-time column):

SET HOLDLIST=PRINTONLY

DEFINE FILE CAR
DT1/HYYMDS = DT(20050601000000);
END

TABLE FILE CAR
PRINT
COUNTRY
COMPUTE DT1A/A8MDYY = HDATE(DT1, 'MDYY'); NOPRINT
COMPUTE DT1B/A10 = EDIT(DT1A,'99/99/9999');

-*ON TABLE PCHOLD FORMAT EXCEL
END

Thanks, Dhagen...we have the 7.6.8 version so the FPRINT doesn't work...

Francis, I did try the code you have below and it works only when the date is hardcoded in the DEFINE. I replaced it with the hiredate column and it gives me this error in my DEFINE.

(FOC177) INVALID DATE CONSTANT: HIREDATE

Here's the code I have...

SET HOLDLIST=PRINTONLY

DEFINE FILE SQLOUT
HIRE/HMDYYIA=DT( hireDate );
END
TABLE FILE SQLOUT
PRINT
userName AS 'Username'
password AS 'Password'
firstName AS 'First'
middleName AS 'Middle'
lastName AS 'Last'
ssn AS 'SSN'
dateOfBirth/HMDYY AS 'DOB'
stateOfLicense AS 'DLState'
licenseNumber AS 'DLNumber'
orderPurpose AS 'OrderPurpose'
hireDate/HMDYY AS 'HireDate'
termDate/HMDYY AS 'TermDate'
subAccount AS 'SubAccount'
referenceAppend AS 'ReferenceAppend'
COMPUTE DT1A/A8MDYY = HDATE(HIRE,'MDYY');NOPRINT
COMPUTE DT1B/A10 = EDIT(DT1A,'99/99/9999');

.....

Thanks!

The DT() "function" can only use a numeric string constant, not a data column.

What is the format of hireDate in the SQLOUT HOLD file? You can determine that by issuing a

?FF SQLOUT

command.

FILENAME= SQLOUT

hireDate E11 HYYMDI

The DEFINE sets up a date-time field like the one you have in your HOLD file - you do not require this in your code.

The COMPUTEs are required - one extracts the date from the date-time field, the other formats an alpha field to look like a date.

SET HOLDLIST=PRINTONLY

DEFINE FILE CAR
hireDate /HYYMDI = DT(20050601000000);
END

TABLE FILE CAR
PRINT
COUNTRY
hireDate 
COMPUTE DT1A/A8MDYY = HDATE(hireDate, 'MDYY');
COMPUTE DT1B/A10 = EDIT(DT1A,'99/99/9999');

-*ON TABLE PCHOLD FORMAT EXCEL
END

This works, Francis...Thank you very much for all the help!