Link to home
Create AccountLog in
Avatar of g-spot
g-spot

asked on

SELECT by day, month etc

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()))
Avatar of Jai S
Jai S
Flag of India image

WHERE        (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()))
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()))
Avatar of Sham Haque
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
>>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....
SOLUTION
Avatar of Jai S
Jai S
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
the same applies for the months too gbshahaq
good point jaiganesh...it's funny that when it comes to months, I forget that other years exist.... :-)
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
@All,

In order to maximise the index usage, try avoinding converting the data as far as possible
Avatar of g-spot
g-spot

ASKER

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.
aneesh is the master, his solution is much proper than mine .....if you think you can split the points tht will be great :-)
>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
Avatar of g-spot

ASKER

How do i get orders for yesterday using Aneeshattingal's method?
WHERE EntryDate >= CAST(CONVERT(VARCHAR(10),GETDATE()-1,120)  as SmallDatetime )  and EntryDate <  CAST(CONVERT(VARCHAR(10),GETDATE(),120)  as SmallDatetime )