Issue with Declaring a variable in SQL Editer

Hi,

I am trying to build a workflow that will end up being uploaded to the server as an analytic app. I am running into an issue where I am trying to create a variable for the date, so that it can be edited by the user on the gallery. When I enter the following code, I run into an error with "Syntax near @". Is there another way to declare this variable?

Thanks!

DECLARE @MYDATE DATE
SET @MYDATE = '2023-02-28"

Workflow

Input

Error Message

Date Time

Server

Apps

Interface Tools

Accepted answers

alexnajm

Then I would use a Formula tool in Alteryx in between the Text Input and the Dynamic Input to calculate your this date (rather than using the DateAdd function) and pass that through the Dynamic Input as well

All comments

alexnajm

Might be insignificant, but shouldn't it be either both double quotes or both single quotes around the date?

Otherwise, instead of declaring you could have the interface tools simply update a placeholder value in the queries and bypass the need to declare a date

adam_rafael_18

Thanks for the response - even with the same (both single or both double) quotes, the issue still persists. We are hoping to declare it in the input, as we want to pull for both that day and the weekday before it through the input tool (we're using SQL here to pull from an ODBC connection)

alexnajm

I still think my option would bypass the need to declare a variable - can you provide a snippet of the code, at least how the declared variable interacts with another part of the code?

alexnajm

Something like this, but it can go in a multitude of directions: Solved: Updating a SQL Query in an App - Alteryx Community

adam_rafael_18

This is the basis for the code with other pieces removed that have no impact

DECLARE MYDATE AS DATE

MYDATE = '2023-03-30'

SELECT *

FROM TABLE

WHERE DATE = MYDATE OR

SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, MYDATE)
WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, MYDATE))

adam_rafael_18

I mistyped the ending but pretty much the where clause is if it is set to the date or the weekday before

alexnajm

Something like this would work, where the SQL Code is in the Input Data tool OR the Dynamic Input tool

Here you do NOT need any declaring! It would go in and replace "MYDATE" with your value

SELECT *

FROM TABLE

WHERE DATE = MYDATE OR

SELECT DATEADD(DAY, CASE DATENAME(WEEKDAY, MYDATE)
WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, MYDATE))

Issue with Declaring a variable in SQL Editer.yxwz

adam_rafael_18

Thanks for this - do you know why the 'DAY' function may not be working? This is my current code, and the DAY function seems to be breaking it

SELECT *

FROM TABLE

where MYDATE in ('2023-01-27',DATEADD(DAY, CASE DATENAME(WEEKDAY, '2023-01-27')
WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1 END, DATEDIFF(DAY, 0,'2023-01-27' )))

alexnajm

I am unsure - lowercase perhaps? SQL Server DATEADD() Function (w3schools.com)

adam_rafael_18

Appreciate the link and help - unfortunately still breaking there, it's saying 'Day is not a column'

alexnajm

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703