TDP 4.2 Set Variable with date into string

Toad Data Point
1

Hi all

I have a problem with set variable activity, when i try to put a formatted date from a dataset to a string variable :

I’m using Oracle 11GR2

example :

-execute script activity :

select to_char(trunc(sysdate, ‘month’), ‘DD/MM/YYYY’) as date_deb

from dual return ‘01/07/2017’

save result set in variable VROD

-set variable

variable name : test

variable type : string

variable value : #VROD.date_deb# (quoted or not)

result : Variable “test” set to ‘0.0021956229194702174374955733’ (the slashes seems to be interpreted as “divide”)

work around is to change variable type as SQL but “prompt during run” become unavalaible…

I have to create specific SQL for setting each variable needed…

Another way ??

Regards.

Franck

2

If your variable type is string and the select result value is string (‘01/07/2017’) I don’t understand how you are getting a numeric result. Try using the result variable without the #'s (i.e. set the variable value to VPROD.date_deb instead of #VPROD.date_deb#). Sometime you have to play with using #'s or not using the #'s

3

Hi Greg,

I would precise something :

The variable is he result of the division : 01 divided by 07 divided by 2017, returned in string …

Franck

4

The variable activity works on a combination of SQL and an expression evaluator. We have difficulty determining if a string is validate or not. So in the code we are trying to cast as date and make a determination on this. It is an area we are trying to improve.

For now, try checking out the various Date functions of the expression evaluator. This one will give you the same format.

To_char(Round(Current_Date(), 'MONTH'),'DD/MM/YYYY')

01/07/2017

dateRound.png
dateRound.png682×524 19.4 KB

5

In addition to the proposed work around I did enter QAT-11011 to fix your original scenario as that should have worked[;)]

6

What do you get if you write #VPROD.date_deb# into the log file, using a log comment step (‘01/07/2017’ or that big decimal number)?

7

Another thing you can do is concatonate a character in front of the date string in the sql i.e. select ‘R’ || to_char(trunc(sysdate, ‘month’), ‘DD/MM/YYYY’). Then set your test variable to right(#VROD…, 10) or replace(#VROD…, “R”, “”) to make Toad see the result as string instead of numeric.

8

I did a test in my environment (MS Sql Server). Same problem. select convert(varchar(10), getdate(), 101) cdate gives me the result ‘07/06/2017’ which is what #VROD.cdate# holds. When I try to set my Test variable to that I get the divided number.

Try something like this as workaround:

select ‘R’ + convert(varchar(10), getdate(), 101) cdate

result into VROD (for Oracle use || intead of + to concatonate and To_char instead of Convert)

Then set the Test variable to:

Replace(’#VROD.cdate#’, ‘R’)

The single quotes are needed or it does not work. Good luck,

9

martin fixed QAT-11011 today. so this fix will be in the next beta. thanks Martin [Y]

10

Thank you all the support team !!!

Franck

11

HI All-

I am aware that the discussion here about variables has to do with Oracle. That is not for me. I am having an issue in SQL trying to set up a date range variable that prompts for the two dates input. I can run it as a regular script either from Editor of Query Builder. Trying to Automate and publish to TIC is causing me a lot of headache… I am sure it is something simple, but I don’t seem to find it. My end goal is to allow non TOAD users to input the date range while logged in to the web server and pull in the dataset needed.

Any assistance will be appreciated!!!

Regards,

Chris

12

I have tried it in the latest version of 4.3 that we are working on and it works in the following way:

  1. add SetVariable activity with two DateTime variables and set Prompt checkbox - For example date_start and date_end

  2. make the variable values to be surrounded by single quotes - For example ‘2015-05-01’

  3. add your desired database activity that is supposed to get data (Execute script activity maybe or Select to file?)

  4. use variables in your SQL query and surround them by single quotes - For example SELECT * FROM myContacts WHERE brith_date between ‘#date_start#’ and ‘#date_end#’ (please note that single quote indicating datetime string literal and the hash sign indicating variable identifier)

However you will have to try this in the next comming beta, because i was not able to achieve this in 4.2 due to the above mentioned datetime bug.

Hope this helps

13

This fix is available in today’s Beta if you would like to try.

www.toadworld.com/…/