Here is my dilemma. In SQL I can get the first day of 2 years ago by using this formula: dateadd(year, -2, datename(year, getdate()) + '0101')
I cannot figure out how to do this in Toad Data Point. Nothing I try works. I have search the net and cannot find anything that works either. Surely others have figured this out.
Help?
So, yes, depending on which data platform you're working with, you should be able to accomplish this with a supported function (or nesting of functions) within the SQL lanaguage.
However, if you're using TDP Pro edition, you might want to check out the Transform & Cleanse capability. In many cases, you can do things more quickly and easily there vs. spending some time getting your SQL syntax right. See snap below, where I created a Calculated Field that subtracts 24 months from one of my date columns.
You can set your variable in Toad Data Point to a SQL value, so you can put that exact SQL code's output into a variable. Just drag a add variable to your automation, name it, pick the type as SQL from the dropdown and put Select + your dateadd(year, -2, datename(year, getdate()) + '0101') as 2yrBackDate in the SQL code box and your good. If you are using an Oracle database add a from Dual on to it. Then you can use that #variablename# in something else in the automation.
