Hello,
I am using export to excel. after download excel data get in date time ,as per requirement format should be "dd-MMM-yy". how can i change date time data in date format . might be screenshot help to understand my question.
Thanks and regards
I think you can work on the data before making the record list to excel. Can you try to use the FormatDateTime(DateTime, "dd-MMM-yy") by changing the attribute DateTime you want to change the format?
https://success.outsystems.com/Documentation/11/Reference/OutSystems_Language/Logic/Built-in_Functions/Format?_gl=1*b0nq9x*_ga*MTc5Nzg1NzgxOS4xNjUyNzQ4Mjk4*_ga_ZD4DTMHWR2*MTY2NzMwNDEwOC4xMjcxLjEuMTY2NzMwNTc4MS4wLjAuMA..#FormatDateTime
After converting, it will give you a text output, so, if you want to maintain the date types, you can use: textotdate(FormatDateTime(DateTime, "dd-MMM-yy")). Also I think you should use a specific structure to use a Date attribute instead of DateTime, with DateTime, if the time is not inserted will be null, so it will give you 2021-10-12 00:00:00.
Let us know if that works, or if there is a better solution I would like to know as well :D
Kind Regards,
Márcio
Thank you for quick response
I tried that format. i think you are saying to add assign before excel like below-
But what result i got not as per requirement-
You need to iterate the list and change each record's date. You did just for the current, which means the first element.
You mean for each loop-
Result of this convert all date as per last date of count and date replaced like below-
That's better! It would be better to look at your code and structures. I don't know what is on the for each and on the assign. Also, I dont know if there are lists inside of lists.
Can you take screenshots of the structure, for each, and the assign? Or you can share an OML
It seems that is always taking the last value of the list.
This is working fine ...before i was selected wrong list.
Thank you so Much...
Hi Shashi Mishra,
You can make the date time attribute to only date.or by formatting from DateTimeToDate() when u are assigning it. or using FormatDateTime().
Regards, Faiz
Thank you Faiz for quick response.
Hi Shasi,
You can largely ignore what others have said here, as their advise is, I think, not correct. If you output an attribute of Date to an Excel using Record List to Excel, the column in Excel will be formatted with "yyyy-mm-dd", when the attribute is of type Date Time, the column in Excel will be formatted with "yyyy-mm-dd hh:mm:ss". Here's an example. I've created the following list, the data types of the attributes follow their name:
The resulting Excel looks like this:
As you can see, the second column shows only a date, the third column shows a date and time, even when I didn't specify a time as in the third row.
This means that any advise about using FormatDateTime (which converts the date time to a Text!) then using a TextToDate (which converts it back to a Date, but because the attribute is Date Time, it then autoconverts to Date Time), or use DateTimeToDate (which will strip the time, but the result will be a time of 0:00) is flawed.
When using Record List to Excel, the only way to get a date instead of a date time in Excel is to set the attribute's type to Date instead of Date Time.
In addition to the above, Márcio pointed me to your requirement of having dd-MMM-yy format. There's unfortunately no way to do that with Record List to Excel and still maintain the column as a type date (as you've noticed). The only way I can think of of satisfying your requirement is to use Advanced Excel, but that has a pretty steep learning curve compared to Record List to Excel.
Hello Kilian,
Thank you so much for reply and explanation.
but if i change attribute type that affect whole project...so i am looking another way if possible.
In that case create a structure that's almost identical to the one you already have, but change the one attribute from Date Time to Date, and assign the Date list to the Date Time list, mapping the Date Time to Date with a DateTimeToDate(). Something like this:
(only then of course with your structures).