Design Studio 1.3 Scripting Example to Convert String Number to Date
The following question was asked in the SCN forum: Convert String to Date – DS 1.2
“Has anyone converted a string date value i.e. 41517 = 8/31/2013 in Design Studio 1.2?
Is there a CAST function or a method that would convert the example to a Date data type?”
I have only the newest version of Design Studio 1.3 so I have used whatever is available in DS 1.3 to accomplish this requirement.
Design Studio 1.3 offers currently the following functions/methods that can be utilized in some way:
I have used the following 4 of them:
I managed to implement a solution via scripting that would extract the year, month and day from a string number like: 41517
See the attached txt file which contains all the scripting code and is the main part of this blog post.
I have used it in the “Application > On Startup” Event.
The code contains also some hopefully useful comments. (actual code is maybe only half of the file, at the top there are a lot of comments and at the bottom only some debug related information)
I hope you can get some new ideas about the scripting possibilities and how can some of them be used in a real world example.
The script is not intended for production use. Further testing would be required. It can be used as a starting point. Some things can possibly be written/accomplished in an other and perhaps better way too.
The main challenge was to figure out a logic to deal with leap years. An interesting information about leap years that was new to me is: “Years that are divisible by 100 are not leap years, unless they’re also divisible by 400.”
But this was not considered in my code logic so far anyway. So starting with the year 2100, dates after that might not be correct. It is left as an exercise to whoever would like to calculate such a date conversion in Design Studio without date functions to think about all the pitfalls, if possible extend the code and test it properly.
I have tested the code for some other string number dates too. I tested it up against MS Excel. If you copy such a number to an excel cell and change the formatting of the cell to a Date format, it will convert it to an actual Date with year month and day. The numbering begins from 1 which equals/represents the date 1.1.1900. Here is the list of numbers that I tested and passed the test comparison with Excel:
My test DS application has only some text components:
Result in a Web Browser (input is the string 41517, output is the correctly extracted date 31th August 2013):
Because of the currently missing built-in Design Studio functions for such date conversions you should rather prepare the data beforehand (BW, ABAP, HANA, SQL, …) in a way you need it later in Design Studio so that you do not need to implement any complicated time-consuming error-prone logic there for something for which already standardized solutions exist in other technologies.
But I do hope that in a future release there will be some further helpful scripting functions for data manipulation that will simplify and accelerate the scripting experience/development.
David - thank you so very much for the comprehensive solution! This is above and beyond.
To your comment regarding your recommendation, I totally agree about preparing the data. I went a step further and had the DDL updated on the base table. However, doing this seemingly caused other issues. If you have any insight, please see the following posting I drafted today.
Aside from the issue, I tried to alter the data type at the Universe (IDT) level trying both at the Data Foundation and Business layers. IDT allowed the CAST and of the date value to Date but the defined data source on the DS side complained. After a couple of other combinations, I asked for the DDL update. Since then, the date column is following through as expected and visiable on the DS side as well. The catch however was as noted in the above thread.
Thanks again for your time and awesome response! Truly appriciated...
I have never worked with Universes/IDT or HANA, only with BEx Queries so far. I/we did experience some problems with BEx+Design Studio in situations when a query was already in use in a design studio application and maybe also opened in it and some changes were made to the data or to the query, Design Studio couldn't recognize these changes or somehow mixed old stuff with new stuff. What definitely helped was to create a new report with such a query from scratch I think. But I didn't investigate the behavior also because we are just evaluating and not using Design Studio in production yet...
I'm glad that in the end you have managed to solve your problems.
The only thing that came to my mind when I was in the middle of reading your comment was that once you have successfully casted/converted the number into a date representation you could convert this Date data back to a string (1/1/2014 once saved in Date format and in an another column or so the same 1/1/2014 but saved again in string format and use only the string format in Design Studio if a string format is sufficient too)...
Thanks for the follow up and the added experiance. Sounds like the object CUID causes some if not all the issues with the Data Source definition on the DS side being different after the DDL update. My colleagues work has to be redone in terms of a new Data Source definition and rebinding within and through the application where referenced.
If I have new learnings I will follow up and post.
Unsurpisingly, a high quality, top-notch post, as always. Thanks for taking the time to share.
try out desing studio 1.3 SP1 and my Simple Date Object SDK:
Design Studio SDK: Simple Date Object Component
it allows also easy rolling by date...
"See the attached txt file which contains all the scripting code and is the main part of this blog post."
— I can not see the attachment , can someone please re post .thanks
did you finally get this txt file? if yes, can you share it for me please?