Toad Data Point 4.3 has a bug when passing dates that are read via SQL (4312620)

Toad Data Point 4.3 has a bug when passing dates that are read via SQL

Toad Data Point 4.3 has a bug in Automation that did not exist in 4.2. Toad Data Point 4.3 has a bug when passing dates that are read via SQL. In the Initialize Variables Activity in Automation, if you create a variable type of SQL and use a SQL statement to select a date from SQL Server, instead of returning a date time variable, you get the SQL string '''To_date('06/01/2017 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')''' which is not recognized as a date. In TDP 4.2, a SQL variable type that selected a date field would return a date.

WORKAROUND:

None

STATUS:

Waiting for a fix in a future release of Toad Data Point.

QAT-13095

In Toad Data Point 4.3 1. Use the Microsoft Access Toad Sample Database 2. Run the attached Automation Script. Step1 creates a table named "Dates" with two columns, START_DATE (datetime) and START_DATE_TEXT (varchar(255)) and inserts a date value '01/01/2018' into the START_DATE column. Step2 creates a SQL variable type named START_DATE, and uses SQL to extract the date value from the START_DATE column in DATES and assign it to the :START_DATE variable Step3 attempts to insert the :START_DATE variable value into the START_DATE column in DATES AND attempts to insert the START_DATE variable value into the START_DATE_TEXT column in DATES You will see error messages saying "Data type mismatch in criteria expression." 3. Now, in your editor, "Select * from DATES" 4. This will be your result set: START_DATESTART_DATE_TEXT '''To_date('01/01/2018 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')''' In other words, in Step2, the variable value for :START_DATE is actually a string ( '''To_date('01/01/2018 00:00:00.00', 'MM/DD/YYYY HH24:MI:SS.SS')''') not a date value, and won't work in any SQL script or database expecting a date value. This isn't an Access database thing. This applies to using the variable type SQL to assign a date value to a variable from any database. Mine is SQL Server. In Toad Data Point 4.2, using the SQL variable type to assign a date value in the Set Variable active.