I have an automation script in Toad Data Point 6.05 that is using several variables. The code I'm using for a character variable in the Set Variable step is:
The #AddDays# variable is a prompted numeric variable with the default value of 0. When I run this today, April 1st, 2024, the resulting variable value is 2019. Toad is interpreting the dashes in the format string as minus signs (2024 minus 4 minus 1). If I use another format such as 'MM/DD/YYYY', Toad is interpreting the forward slash as a division symbol.
When I remove the #AddDays# variable and replace it with a zero, the To_char function produces the correct result. Is there any workaround I can do to get my variable in the correct format?
I'm not seeing this, at least not on my TDP 6.2 against Oracle 18c. See snap below, where I created a quick automation script that sets two variables like you mentioned, and then logged the values which are being picked up by the variables. Note that I used a -1 as the AddDays input on prompt, and it produced (correctly) yesterday's date. Note also that the variable type of my date is of String type.
You didn't mention what database you're working with, but dates and date conversion functions can be tricky to figure out across the multi-flavored database landscape. It's usually helpful to use the "Log Comment" task to confirm how your variable values are getting set during your automation runs. Hope this helps.
It's SQL Server, but is TDP even reaching out to the database to set this variable value? Even so, why would a hard-coded numeric value work correctly as opposed to a numeric value in the #AddDays# variable? Perhaps there was a bug that was resolved above 6.05?
Hi @ketpt1,
I was successful in reproducing the issue using TDP 6.0.5 and SQL Server. With little experimentation, I found that if you use "_" instead of "-" ('YYYY_MM_DD' instead 'YYYY-MM-DD'), result is different:
If this helps, great. If not, I would recommend opening a support ticket, so we can investigate the problem thoroughly.
Unfortunately, a date in that format is not a valid date in SQL Server. Also, I assume it's working because an underscore is not a mathematical operator.
