Pass a Variable into SQL Query on Execute Query Activity?

Help Studio
1

Hi all,

I have Execute Query activity where I want to pass a variable in as the date. The variable is a string variable containing yesterday’s date (System.DateTime.Today.AddDays(-1).ToString(“yyyy-MM-dd”))

var = yesterdayDate

Here is part of my SQL query below. I want to replace ‘2020-04-07’ with the yesterdayDate variable. The time will remain the same so no need to change that part:

between to_date(‘2020-04-07 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’)
and to_date (‘2020-04-08 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’)

Here is how I tried, but it doesn’t seem to be working:

between to_date(“+”’”+yesterdayDate+”’+ 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’)
and to_date (‘2020-04-09 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’)

2

Not sure if this is true of youre version of SQL, but in T-SQL, the GETDATE() function returns the current datetime in SQL. So you can get yesterday’s date within the query itself with DATEADD(DAY, -1, GETDATE()) within the query. No need for parameters.

1 Like
3

@Anthony_Humphries I’m using oracle database…not too familiar with sql so not sure. I will try to see if I can do that within the query itself.

4

It looks like you want SYSDATE, based on the article below. Just subtract a day from it.

1 Like
5

@Anthony_Humphries Thanks. I’ve tried a couple different ways from researching online but I’m getting a date format not recognized error when I run the query.

6

I’ve also tried this in the UiPath sql editor:

image
But still no luck…

7

“between to_date('”+ yesterdayDate +”’ 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’) and to_date (‘2020-04-09 12:03:00 PM’ , ‘YYYY-MM-DD HH:MI:SS PM’)"

1 Like
8

@Doanh So I tried this in my sql statement and received the error:

Execute Query: ERROR [HY000] [Oracle][ODBC][Ora]ORA-00911: invalid character