Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

SQL query for last week's monday

Ronal_bal
8 - Asteroid

How to pull the data from last week's Monday based on current system time?

I tried to use getdate() but its invalid function.

current_date is working. 
DateTime(DATEDIFF(CURRENT_DATE, 0, CURRENT_DATE), 0)

The above shows arguments mismatch error. any idea?

2 REPLIES 2
MatthewO
Alteryx Alumni (Retired)

@Ronal_bal here is an Alteryx way of calculating the preceding Monday's date. I believe similar functions exist in SQL Server. Alternatively, you could conduct the calculation in Alteryx, then use a Dynamic Input tool to update the query with value calculated in the workflow.

image.png

MRod
8 - Asteroid

To select records for the last 7 days

SELECT * FROM [TableName]
WHERE Created_Date >= DATEADD(day, -7, GETDATE())

To select records for the current week

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT * FROM [TableName]
WHERE CreatedDate >= DATEADD(day, 1 - DATEPART(WEEKDAY, GETDATE()), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 8 - DATEPART(WEEKDAY, GETDATE()), CONVERT(DATE, GETDATE()))

if you want to select records for last week instead of the last 7 days

SET DATEFIRST 1 -- Define beginning of week as Monday
SELECT * FROM [TableName] 
WHERE CreatedDate >= DATEADD(day, -(DATEPART(WEEKDAY, GETDATE()) + 6), CONVERT(DATE, GETDATE())) 
  AND CreatedDate <  DATEADD(day, 1 - DATEPART(WEEKDAY, GETDATE()), CONVERT(DATE, GETDATE()))

 

Labels
Top Solution Authors