Determine date for Monday of current week in t-sql

I've been asked to combine two sets of data in a query.

the first set needs to come from one table that was entered on or before noon on Monday of the current week.

the second set  comes from another table  and will show only the data that was available after that same date time.

I know that I need to do this as a union statement...but I don't know how to get the date time for the Monday date programatically.
thedeltacompaniesAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

to get Mondays date of this week

declare @MondayDate Datetime
SET @MondayDate = DATEADD(dd, -DATEPART(dw, GETDATE()) + 2, GETDATE())
SELECT @MondayDate

or simply

SELECT DATEADD(dd, -DATEPART(dw, GETDATE()) + 2, GETDATE())
0
 
Aaron ShiloChief Database ArchitectCommented:
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek

0
 
thedeltacompaniesAuthor Commented:
Because we have a calendar table with the weekstart date and Monday as first day of week, I was able to calcuate the date using the calendar and a case statement:  

(SELECT     (CASE WHEN LS.KPI_NKI_Actuals_Day = 'Monday' THEN
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Tuesday' THEN 1 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Wednesday' THEN 2 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Thursday' THEN 3 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Friday' THEN 4 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Saturday' THEN 5 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) WHEN LS.KPI_NKI_Actuals_Day = 'Sunday' THEN 6 +
                                                               (SELECT     C.WeekStart
                                                                 FROM          Reporting.dbo.Calendar C
                                                                 WHERE      C.CalendarID = FLOOR(CONVERT(float, getdate()))) END) + CONVERT(datetime, KPI_NKI_Actuals_Time) AS Expr1
                                     FROM         LeadershipAdminApplication.dbo.Lockdown_Settings AS LS)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.