?
Solved

SELECT by day, month etc

Posted on 2007-09-28
14
Medium Priority
?
239 Views
Last Modified: 2010-03-20
How do I create a Select statement that will select all orders this month or all orders today or all orders last month etc.

I guess something like this:

WHERE        (EntryDate = DATEPART(month, GETDATE()))
0
Comment
Question by:g-spot
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 14

Expert Comment

by:Jai S
ID: 19977703
WHERE        (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()))
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19977709
this month
WHERE        (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()))
last month
WHERE        (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()) -1)
today
WHERE        (DATEPART(day, EntryDate) = DATEPART(day, GETDATE()))
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19977716
all orders this month
WHERE  DATEPART(month, EntryDate) = DATEPART(month, GETDATE())

all orders today
WHERE CONVERT(varchar(10),EntryDate,101) = CONVERT(varchar(10),GETDATE(),101)

all orders last month
WHERE  DATEPART(month, EntryDate) = DATEPART(month, GETDATE())-1
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Sham Haque
ID: 19977724
>>today
WHERE        (DATEPART(day, EntryDate) = DATEPART(day, GETDATE()))

i would not use this!! this will return all orders on the same day of month as current day

eg. today is 28th October 2007 - will SELECT recs with an EntryDate of 28th of any month and year....
0
 
LVL 14

Assisted Solution

by:Jai S
Jai S earned 200 total points
ID: 19977728
this is correct...i missed out the years in the prev post
all orders this month
WHERE  (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()) and
DATEPART(year, EntryDate) = DATEPART(year, GETDATE()))

all orders today
WHERE CONVERT(varchar(10),EntryDate,101) = CONVERT(varchar(10),GETDATE(),101)

all orders last month
WHERE  (DATEPART(month, EntryDate) = DATEPART(month, GETDATE())-1 and
DATEPART(year, EntryDate) = DATEPART(year, GETDATE()))
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19977730
the same applies for the months too gbshahaq
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 19977740
good point jaiganesh...it's funny that when it comes to months, I forget that other years exist.... :-)
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 19977742
-- all the orders this month
WHERE EntryDate >= CAST(CONVERT(VARCHAR(8),GETDATE(),120)+'01' AS smallDATETIME) AND EntryDate < DATEADD(m,1,(CONVERT(VARCHAR(8),GETDATE(),120)+'01' ) )

-- today
WHERE EntryDate >= CAST(CONVERT(VARCHAR(10),GETDATE(),120)  as SmallDatetime )

--lastMonth
WHERE  EntryDate >=  DATEADD(m,-1,(CONVERT(VARCHAR(8),GETDATE(),120)+'01' ) ) AND EntryDate < CAST(CONVERT(VARCHAR(8),GETDATE(),120)+'01' AS smallDATETIME)

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19977764
@All,

In order to maximise the index usage, try avoinding converting the data as far as possible
0
 

Author Comment

by:g-spot
ID: 19977831
Wow, thanks for all your double quick replies.

Can I just ask which is the more correct solution between aneeshattingal and jaiganeshsrinivasan?

They both seem to do the same thing although aneeshattingal 's is mor difficult to read.

Thanks.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 19977841
aneesh is the master, his solution is much proper than mine .....if you think you can split the points tht will be great :-)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19977879
>They both seem to do the same thing although aneeshattingal 's is mor difficult to read.


eventhough it is difficult to read, performance will be higher, if you create an index on EntryDate
0
 

Author Comment

by:g-spot
ID: 19978026
How do i get orders for yesterday using Aneeshattingal's method?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19978062
WHERE EntryDate >= CAST(CONVERT(VARCHAR(10),GETDATE()-1,120)  as SmallDatetime )  and EntryDate <  CAST(CONVERT(VARCHAR(10),GETDATE(),120)  as SmallDatetime )
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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