Trying to pass start date and end date variables into SQL query

Help Studio
, , , ,
1

Hello all,

I am creating a workflow that starts off asking the user to input a start-date and an end-date. Once entered, the workflow should execute a query that filters the full table down to just the rows where transaction date fall between the start date and end date. The actual SQL query is in the variable “SQL”

I am attaching my workflow and excel data table below. The data table is in a microsoft SQL server table.
DateTime Test.xaml (10.0 KB) RPADummyData.xlsx (13.3 KB)

How do I get the SQL code to dynamically accept the start date and end date variables to filter the data table down?

Any help would be appreciated.

2

Looking at your workflow, you have pretty much done it. By dynamic if you mean you just want to create a separate module that accepts input dates and gives you DataTable then convert the date variables to argument and then properly format the values.

1 Like
3

@hall1570
Declare parameters in execute query activity and pass the value as we do in argument and in SQL query use that variable as we do in SQL statement

1 Like
4

When I run the workflow, it works but the table “dbo.testDummy2” is empty.

5

Hi Sandeep,

I am new to UiPath so i’m not sure what that means. I just noticed the parameters on the edit SQL window. How would I need to do this?

I’ve gotten some success using the &stateDate& AND &endDate& variables but I can’t get the syntax correct.

Can you take a look at this too @Palaniyappan

1 Like
6

Hi
Mention the parameters along the property and in query mention as @argumentname that is with @symbol in from of the argument name
Cheers @hall1570

7

Not sure I understand, would you mind attaching the xmal?

8

I think I almost have it. I am posting my modified workflow below.

The only error I am getting now is: “Execute Query: Conversion failed when converting date and/or time from character string.”

Main.xaml (8.9 KB)

In this version, I am using 2 input dialog activities to get a start date and end date string variable.

Then I am using 2 assign activities to convert these string variables to DateTime variables using the following code: DateTime.ParseExact(startDate,“yyyy-mm-dd”,New system.Globalization.CultureInfo(“en-US”))

AND

DateTime.ParseExact(EndDate,“yyyy-mm-dd”,New system.Globalization.CultureInfo(“en-US”))

Then my execute query activity attempts to dump these 6 transactions from the list into a new table called “testDummy2”.

This code works when I manually type the dates in:

Blockquote
INSERT INTO ArgoPro.dbo.TestDummy2
SELECT testDummy.[Month_Number], testDummy.[Month], testDummy.[Year],
testDummy.[Employee], testDummy.[Manager], testDummy.[Company], testDummy.[Division],
testDummy.[Department], testDummy.[Report_Date], testDummy.[Last_Submitted_Date],
testDummy.[Delegate], testDummy.[Report_Name], testDummy.[Purpose], testDummy.[City_Location],
testDummy.[Expense_Type], testDummy.[Account_Code], testDummy.[Transaction_Date], testDummy.[Vendor],
testDummy.[Entry_Key], testDummy.[Payment_Type], testDummy.[Personal], testDummy.[Expense_Amount]
FROM ArgoPro.dbo.testDummy
WHERE Transaction_Date Between ‘2018-11-15’ AND ‘2019-01-01’

Blockquote

How can I replace the two dates in the Where statement above with dynamic variables in the execute query activity?
@Palaniyappan @sandeep13 @Ishmeet_Bindra

2 Likes
9

@hall1570
I am out of station .not able to see your workflow.
Are you pass start date and end date in execute query parameters property?
If yes than in SQL query use this parameter as @startdate and @ enddate

Try convert string to date in SQL query rather then assign activity

1 Like
10

I am able to pass the variable using the following syntax:
‘+StartDate+’ AND ‘+EndDate+’

but now I am getting the following error:
Execute Query: Conversion failed when converting date and/or time from character string.

All I am working on doing is getting the variable to read into the actual SQL code which should work I would think.

1 Like
11

Fine
Did we check with this thread buddy

Cheers @hall1570

12

@hall1570, Just ensure your dates are in “yyyy-MM-dd” format and it should work.