date of the month

I have two fields called orderdate_d, clinetname, how can I retrieve
clientname whose order date is from the 10th of last month to today?
Thanks,select clientname
from yourTable
where orderdate_d between '8/10/2005' and getdate()
Derek Davis
ddavis76@.gmail.com
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:734C2A76-4770-48D2-8611-BA0D0EDBDA90@.microsoft.com...
>I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||... where orderdate_d >=dateadd(month, datediff(month, 0, getdate())-1, 0)+
9
and orderdate_d < datediff(day, 0, getdate()+1)
qjlee wrote:

>I have two fields called orderdate_d, clinetname, how can I retrieve
>clientname whose order date is from the 10th of last month to today?
>Thanks,
>|||SELECT clinetname FROM table_name
WHERE orderdate_d >= DATEADD(MONTH,-1,
DATEDIFF(DAY,-10,GETDATE())-DAY(GETDATE()))
(I assume you're not going to have order dates of tomorrow and beyond.)
"qjlee" <qjlee@.discussions.microsoft.com> wrote in message
news:734C2A76-4770-48D2-8611-BA0D0EDBDA90@.microsoft.com...
>I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||> ... dateadd(month, datediff(month, 0, getdate())-1, 0)+9
Ooh, this is clever too, me likey|||:)
Aaron Bertrand [SQL Server MVP] wrote:

>Ooh, this is clever too, me likey
>
>|||Sorry, I should be more clearly say that this query will have to run evey
month on 11th. How I can specify the year part.
Thanks,
"qjlee" wrote:

> I have two fields called orderdate_d, clinetname, how can I retrieve
> clientname whose order date is from the 10th of last month to today?
> Thanks,|||> Sorry, I should be more clearly say that this query will have to run evey
> month on 11th. How I can specify the year part.
You don't need to, that's the beauty of using DATEADD/DATEDIFF, it doesn't
get the year by parsing a stupid string. You can try it by changing your
clock in your dev environment to december of this year and then january of
next year.|||Thank you all.
It works.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You don't need to, that's the beauty of using DATEADD/DATEDIFF, it doesn't
> get the year by parsing a stupid string. You can try it by changing your
> clock in your dev environment to december of this year and then january of
> next year.
>
>|||On Wed, 14 Sep 2005 14:18:34 -0500, Trey Walpole wrote:

> ... dateadd(month, datediff(month, 0, getdate())-1, 0)+9
Hi Trey,
I agree with Aaron :-)
But it can be made even shorter (not in number of characters, but in
number of operations - and, IMO, easier to understand):
DATEADD(month, DATEDIFF(month, '20050201', getdate()), '20050110')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)