A date in an Impromptu report is displayed in the format DD/MM/YYYY or DD.MM.YYYY (for example: 12/31/2005 or 01.10.2004).
When the report is exported to a CSV, the date is in the following format: YYYY-MM-DD HH:MM (for example: 2004-10-01 12:00).
Why is this happening? How is the date format determined, when saving an Impromptu Report as a text or CSV file?
Use the Save as XLS with Format option in the Impromptu Save As dialog.
When exporting data to a CSV file from any of Impromptu, Impromtu Web Reports (IWR) or IBM Cognos Query, the date format used is the international ISO standard, which is:
- YYYY-MM-DD HH:MM:SS:TH (24 hour clock) for Date Time data types
- YYYY-MM-DD for date data types.
Steps:To preserve the date format displayed in Impromptu, use the
Save as XLS with Format option in
the Impromptu
Save As dialog box.
You can also create a calculated column to convert the date into a formatted string, using the day, month and year or substring functions. For example:
(substring(date-to-string(date),7,2))+ '-' + (substring(date-to-string(date),4,2))+ '-' + (substring(date-to-string(date),1,2))
[{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Impromptu","Platform":[{"code":"PF033","label":"Windows"},{"code":"PF025","label":"Platform Independent"}],"Version":"Cognos 8 BI Report Studio 8.1;Impromptu 7.0;Impromptu 7.1;Impromptu 7.3;Impromptu 7.4;Impromptu Web Reports 7.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU053","label":"Cloud \u0026 Data Platform"},"Component":"Report Studio","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"","label":""}},{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Impromptu","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSTQPQ","label":"IBM Cognos Series 7 PowerPlay"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Impromptu Web Reports","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]