Date format in CSV is coming as MM/DD/YYYY. I'm trying for the format YYYY-MM-DD. Tried different ways and its not working for me. Any inputs or workaround will be appreciated. Thanks.
When I had a similar problem, I used something like this to force the date into the correct format
_year(cast([Date], date))
+ '-' +
_month(cast([Date], date))
+ '-' +
_day(cast([Date], date))
Not the most elegant solution, but it worked.
Thanks Johnson for your response. Tried your code and it isnt working as expected. I'm using current_Date as date and backend is SQL server and the result is coming as 2041.
Good Friend,
Sorry the code didn't work for you. 2041 is the sum of the year, month, and day. Any chance your code looks something like this?
_year(current_date)
+
_month(current_date)
+
_day(current_date)
If the date separators ('-') aren't included in the Data item expression then the result is evaluated mathematically since the '+' operator is being used for concatenation and all the parts are numeric.
If you replace '+' with '||' in the above code
_year(current_date)
||
_month(current_date)
||
_day(current_date)
then you should get '2026115'.
If you include the date separators in the original code above
_year(current_date)
+ '-' +
_month(current_date)
+ '-' +
_day(current_date)
then you should get '2026-1-15'.
Combining both suggested changes yields
_year(current_date)
|| '-' ||
_month(current_date)
|| '-' ||
_day(current_date)
which will also return '2026-1-15'.
Having said all that, I just noticed that the code I suggested will only return a single digit for month for any month before October and you requested MM. I have another, inelegant, solution for that. The code looks at the month value and concatenates a '0' if it is less than 10.
With date separators:
_year(current_date)
|| '-' ||
if (_month(current_date) < 10)
then ('0')
else ('')
||
_month(current_date)
|| '-' ||
_day(current_date)returns 2026-01-15
Without date separators:
_year(current_date)
||
if (_month(current_date) < 10)
then (0)
else (null)
||
_month(current_date)
||
_day(current_date)returns 20260115
To summarize
- Both || and + can be used for concatenation. Using the + operator to concatenate can result in unexpected addition when all values are numeric.
- Data formatting for csv/Excel Data can be a bit of a pain in my experience.
Thanks again for spending time on this. Tried your logic but somehow it is not working as expected for CSV. I got a workaround for CSV with the below logic but when you export it to CSV you will have something like =" 2026-01-15 " and got to format it little bit to show it as 2026-01-15. Below is the Code
Code:
'=" ' + cast(extract(year, current_date), varchar(4)) + '-' +
(if (extract(month, current_date) < 10) then ('0') else ('')) + cast(extract(month, current_date), varchar(2)) + '-' +
(if (extract(day, current_date) < 10) then ('0') else ('')) + cast(extract(day, current_date), varchar(2)) + ' "'
What's with the = and the quotes? Are those needed in your CSV?
Quotenot working as expected for CSV
Not working for CSV? Or not working for Excel? Excel doesn't understand CSV files. You might try looking at the result in a text editor.
Here's what I get using database-agnostic Cognos functions and operators:
cast(_year (current_date), varchar(4)) || '-' ||
substring('00' || cast(_month(current_date), varchar(2)), 1 + char_length(cast(_month(current_date), varchar(2))), 2) || '-' ||
substring('00' || cast(_day (current_date), varchar(2)), 1 + char_length(cast(_day (current_date), varchar(2))), 2)
Thanks Doug for responding on this and this is a CSV issue. I just ran below code and the CSV output is generating the Date format as 01/23/2026 instead of 2026-01-23. Users are expecting YYYY-MM-DD format in csv. Please let me know if there is any workaround this. Thanks.
Code:
cast(_year (current_date), varchar(4)) || '-' ||
substring('00' || cast(_month(current_date), varchar(2)), 1 + char_length(cast(_month(current_date), varchar(2))), 2) || '-' ||
substring('00' || cast(_day (current_date), varchar(2)), 1 + char_length(cast(_day (current_date), varchar(2))), 2)
1. What is the data type of the column which is the source of your data?
2. When you test that column, what does the data look like?
3. If you use the cast function to cast the column to date what does the data look like?
4. Can you answer Doug's question. Are you using a text editor to look at the CSV output?
I'm using the current date and converting it to a varchar format. It displays correctly as YYYY-MM-DD in HTML and Excel outputs. However, when exporting to CSV, the format changes to YYYY/MM/DD, regardless of the adjustments made in the report expression.
Users rely on the CSV output as a source file for downstream imports, so they need the date to be consistently formatted without requiring any manual changes after download.
You are not answering the questions. I don't know if that is because you don't understand them or not. Your responses lack sufficient context. Please do try to answer the questions.
QuoteYou are not answering the questions.
That is correct. Still unanswered...
QuoteNot working for CSV? Or not working for Excel? Excel doesn't understand CSV files. You might try looking at the result in a text editor.
Basically, if you're using Excel to open a CSV file, you're doing it wrong.
And if the requirement is to display data in Excel, use the Excel output format, not CSV.
I'm sorry if I couldnt answer your questions properly earlier.
1. What is the data type of the column which is the source of your data?
Using Current_Date so it is a DATE data type
2. When you test that column, what does the data look like?
In CSV the format changes to YYYY/MM/DD
3. If you use the cast function to cast the column to date what does the data look like?
I casted it to varchar and HTML AND EXCEL gives YYYY-MM-DD which is the desired format but users are running this report in CSV and exporting it
4. Can you answer Doug's question. Are you using a text editor to look at the CSV output?
They are downloading the CSV file and opening the .CSV as is
Still didn't answer the questions.
2. So, you're viewing the output in Notepad and the format of that column is YYYY/MM/DD?
4. Opening the CSV how? Is the user opening the CSV file in Notepad? Or are they allowing Excel to mess up the data?
Quote from: Good Friend on 27 Jan 2026 02:01:52 PM2. When you test that column, what does the data look like?
In CSV the format changes to YYYY/MM/DD
No, when you test that column in CA, preferably in the modelling tool which you are using. Also, when you test that column in your DB's administrative tool.
The object of these exercises is to identify where in the chain things go wrong.
Quote from: Good Friend on 27 Jan 2026 02:01:52 PM3. If you use the cast function to cast the column to date what does the data look like?
I casted it to varchar and HTML AND EXCEL gives YYYY-MM-DD which is the desired format but users are running this report in CSV and exporting it
No, when you do the cast it to date and view it in CA. Also, why, if your column is a date data type, are you casting it to varchar?
Off the top of my head I don't know what variations native date data type columns can have for each DB vendor. Certainly if it is cast to date in Cognos it will be rendered in the ISO 8601 format, which is YYYY-MM-DD.