Records last month

Today's month is 9 as in September. How can I retrieve records entered last month?

This is what I got so far...

WHERE (Classes.CLWhenDt >= DATEADD([MONTH], - 1, GETDATE()))

WHERE MONTH(Classes.CLWhenDt)=8

OR

WHERE Classes.CLWhenDt > '07/31/2006' AND Classes.CLWhenDt <'09/01/2006'

|||

OR

WHERE DATEDIFF(m,Classes.CLWhenDT,getutcdate())=1

Or this will perform faster:

WHERE Classes.CLWhenDT>=DATEADD(m,DATEDIFF(m,0,getutcdate())-1,0) AND Classes.CLWhenDT<DATEADD(m,DATEDIFF(m,0,getutcdate()),0)

|||Thanks for all your replies. I used this one "WHERE Classes.CLWhenDt > '07/31/2006' AND Classes.CLWhenDt <'09/01/2006'" as a temp solution but I will try your reply Motley.|||

Actually, that will catch dates that part way through 7/31. You should use:

WHERE Classes.CLWhenDt>='08/01/2006' AND Classes.CLWhenDt<'09/01/2006' instead, unless you are sure that CLWhenDt never contains a time component (aka always set to midnight).

records for the last 6 months?

I need to retrieve records for the last 6 months so I have the following in my query:
created < (getdate() - 180)

Of course not all months are 30 days so this isn't 100% accurate. I need to be 100% accurate but haven't found a better solution.

Can someone suggest a better solution?

Thanks.
Maybe CREATED < dateadd (mm, - 6, getdate()) ?|||

Depends on exactly what you mean by 6 months..

You could say

select dateadd(month,-6,getdate())

So for today, it would be 2006-11-16 15:01:04.490 since my current getdate is 2007-05-16 15:01:04.490. I think this actually makes the most sense, as it does not really concern itself with the number of days.

However, if you need the fixed number of days to give you a consistent comparison, 180 days is a good thing too.)

Record locking...

Hi Experts,
Here's the scenario of the case...
User1 does a retrieve of information, with the intention of updating it
later. At the same time, User2 does a retrieve of information, with the
intention of updating it later. User1 updates some of the row on the web
server, and then calls an update stored procedure.Then user2 updates some of
the row on the web server, and then calls an update stored procedure.
Some of user1s updates are lost because user2 had "stale" data.
So what a lot of applications do is to implement explicit locking, so that
when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
update it later". The rule is in that case that a locked record cannot also
be locked by someone else
and update can only be called if the record was locked by that user.
We can of course add a column to every table that has to support this
"locking", but I was wondering if SQL Serversystems support some kind of
"lock this record please"?Here's an old archive on this type of issue:
http://tinyurl.com/5qevd
-oj
"Lia" <Lia@.discussions.microsoft.com> wrote in message
news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
> Hi Experts,
> Here's the scenario of the case...
> User1 does a retrieve of information, with the intention of updating it
> later. At the same time, User2 does a retrieve of information, with the
> intention of updating it later. User1 updates some of the row on the web
> server, and then calls an update stored procedure.Then user2 updates some
> of
> the row on the web server, and then calls an update stored procedure.
> Some of user1s updates are lost because user2 had "stale" data.
> So what a lot of applications do is to implement explicit locking, so that
> when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
> update it later". The rule is in that case that a locked record cannot
> also
> be locked by someone else
> and update can only be called if the record was locked by that user.
> We can of course add a column to every table that has to support this
> "locking", but I was wondering if SQL Serversystems support some kind of
> "lock this record please"?|||Hi Experts,
What is usually the system? Is it the person who updates gets the priority
or the person who retrieves (for update) gets a priority.
I have been debating on this for some time and after reading a few books i
got the impression that LOCKING records was the practice in the olden days
and in the current situation ( adLockOptimistic ) the person who clicks
update first get a priority and the other person gets an ERROR! Which can be
trapped using ON Error GOTO statement
Please clarify and help me resolve this issue.
Thanks
Manish
"oj" wrote:

> Here's an old archive on this type of issue:
> http://tinyurl.com/5qevd
>
> --
> -oj
>
> "Lia" <Lia@.discussions.microsoft.com> wrote in message
> news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
>
>|||Hi Manish,
Thanks for your reply on my post. :-) The system will supposed to give the
priority to person who retrieves(for update) in such a way that upon
retrieving, the record will be locked. The lock will then be released after
updating the record or canceling the transaction. And if the other person
select the same record that intend to update it also, message will be given
to inform the person that the record can only be viewed and cannot be update
d
because it is locked for update by another user.
Your suggestions and comments will be highly appreciated.
Thanks,
Lia
"Manish Sawjiani" wrote:
> Hi Experts,
> What is usually the system? Is it the person who updates gets the priority
> or the person who retrieves (for update) gets a priority.
> I have been debating on this for some time and after reading a few books i
> got the impression that LOCKING records was the practice in the olden days
> and in the current situation ( adLockOptimistic ) the person who clicks
> update first get a priority and the other person gets an ERROR! Which can
be
> trapped using ON Error GOTO statement
> Please clarify and help me resolve this issue.
> Thanks
> Manish
> "oj" wrote:
>