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.