For developers who do not regularly program in SQL, there is a constant need for handy tips that help to ease the pain of accomplishing what they need. I work in MSSQL regularly but am no means a 10, if you know what I mean. That is why I thought I would create this category where I could drop the occasional SQL string and hopefully help some others out on their project.
Today’s MSSQL tip is a quick and efficient way to get all records entered on the current week.
First the obvious part
SELECT * FROM [ Your Table]
Now for the tricky Part
WHERE [Your TimeStamp Field] >= (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek)
So to put it all together
SELECT SUM(time) as weekTime FROM timeMaster WHERE [timestamp] >= (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek)
Would get you the SUM of the field time for ALL entries entered on or after Monday of the current week.