SQL query for last week's monday
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Ronal_bal
8 - Asteroid
‎06-15-2022
08:39 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Labels:
- Labels:
- Database Connection
- Date Time
2 REPLIES 2
MatthewO
Alteryx Alumni (Retired)
‎06-15-2022
11:35 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
MRod
8 - Asteroid
‎06-15-2022
10:53 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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()))
