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()))
g-spotAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
-- 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
 
Jai STech ArchCommented:
WHERE        (DATEPART(month, EntryDate) = DATEPART(month, GETDATE()))
0
 
Jai STech ArchCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Sham HaqueSenior SAP CRM ConsultantCommented:
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
 
Sham HaqueSenior SAP CRM ConsultantCommented:
>>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
 
Jai STech ArchCommented:
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
 
Jai STech ArchCommented:
the same applies for the months too gbshahaq
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
good point jaiganesh...it's funny that when it comes to months, I forget that other years exist.... :-)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
@All,

In order to maximise the index usage, try avoinding converting the data as far as possible
0
 
g-spotAuthor Commented:
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
 
Jai STech ArchCommented:
aneesh is the master, his solution is much proper than mine .....if you think you can split the points tht will be great :-)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>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
 
g-spotAuthor Commented:
How do i get orders for yesterday using Aneeshattingal's method?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
WHERE EntryDate >= CAST(CONVERT(VARCHAR(10),GETDATE()-1,120)  as SmallDatetime )  and EntryDate <  CAST(CONVERT(VARCHAR(10),GETDATE(),120)  as SmallDatetime )
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.