Issue with Declaring a variable in SQL Editer

[adam_rafael_18]

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"

[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))

[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)