Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query  - show records where a date field is this week

Posted on 2015-02-03
9
Medium Priority
?
99 Views
Last Modified: 2015-02-10
I need to query to return all records where [completed date] was this week...

Also, i need to do the same for month
0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40586954
This month:

WHERE [completed date] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND
    [completed date] < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

This week is very similar but depends on how you define a week - what day does your week start on?
0
 

Author Comment

by:vbnetcoder
ID: 40587059
I think week is going to be Sunday --> Saturday ...ie just a normal week
0
 

Author Comment

by:vbnetcoder
ID: 40587092
could you give me that query too so i don't mess it up?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40587103
WHERE [completed date] >= DATEADD(DAY, -DATEDIFF(DAY, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) % 7,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
    AND [completed date] < DATEADD(DAY, -DATEDIFF(DAY, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) % 7 + 7,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

You can simplify it if you add a variable for the current day with time stripped.


DECLARE @today_midnight datetime
SET @today_midnight = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

WHERE [completed date] >= DATEADD(DAY, -DATEDIFF(DAY, 6, @today_midnight) % 7, @today_midnight) AND
    [completed date] < DATEADD(DAY, -DATEDIFF(DAY, 6, @today_midnight) % 7 + 7, @today_midnight)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40587119
Similarly, for the month:

DECLARE @start_of_month_midnight
SET @start_of_month_midnight = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

...
WHERE [completed date] >= @start_of_month_midnight AND
     [completed date] < DATEADD(MONTH, 1, @start_of_month_midnight)
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40588099
If you don't have any index on the [completed date] column you can use the DATEPART function. It's more easy for read:
-- Select records within same week
SELECT *
FROM YourTableName
WHERE DATEPART(week, [completed date]) = DATEPART(week, GETDATE())

-- Select records within same month
SELECT *
FROM YourTableName
WHERE DATEPART(month, [completed date]) = DATEPART(month, GETDATE())

Open in new window


NOTE: You need to check what's the weekday set as first day of week. For that run this command: SELECT @@DATEFIRST (1-Monday, ..., 7-Sunday)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40588781
>> If you don't have any index on the [completed date] column <<

But an index could be added next week.  It's very poor practice to deliberately exclude indexes from consideration, particularly due to lazy coding.

You really should always avoid using functions on table columns in a WHERE or JOIN whenever possible.  One, ISNULL, should never be used in a WHERE or JOIN, because it can always be easily coded around.

But, if you do use the month,  you need to include a year check for accuracy, or you'd get every year's rows from the table.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40588858
But, if you do use the month,  you need to include a year check for accuracy, or you'd get every year's rows from the table.
Yes, that's true. I've missed the year in my suggested solution.
0
 

Author Closing Comment

by:vbnetcoder
ID: 40600529
ty
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question