In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

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