SQL Query condition help in Where Clause SQL Server 2005

Hello Experts,

I have a Condition in my query , where I need a Report on Monthly basis.If the Date is =01/01/2012 then, I need the date till Today, like this:

Date
-----
01/01/2012
01/02/2012
01/03/2012
01/04/2012

If the date is :12/01/2011(Prevoius Month)

Date
------
12/01/2011
12/02/2011
12/03/2011
12/04/2011
12/05/2011
.
.
.
.
12/31/2011


I need whole month, since the date is passed. But if I select this month, it should show for 4 days(since today is: 01/04/2012).

I had condition like this in my where clause, which gets only Month and Year.
 
WHERE datepart(mm,users.[datetime]) = datepart(mm,@dt)
And datepart(yy,users.[datetime]) =  datepart(yy,@dt)

Open in new window


NOTE: Here @dt is the temp variable, to pass value.
ASPDEVAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlokJain0412Commented:
hi
please  check what @dt variable type and  @dt variable date format it is returning


0
ASPDEVAuthor Commented:
I have @dt variable as nvarchar(25), when I pass value for instance:12/01/2011, I get the result set of whole month of December., but  when I ran 01/01/2012, I need just 4days of data.

With the above query, it's fine with the previous months, not good for Current month( I mean it give mean whole month again, where I just need  4 days, since today:01/04/2012).
0
AlokJain0412Commented:
I think there is problem  in date type and date format  while  comparison   between
field users.[datetime])  and  datepart(mm,@dt)  or users.[datetime])  and  datepart(yy,@dt)

you please check again date field value and date format

Variable date format and table field format should be same
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ralmadaCommented:
try this
where users.[datetime] between 	dateadd(m, datediff(m, 0, @dt), 0) 
				and 
				case when month(@dt) = month(getdate()) and year(@dt) = year(getdate()) then @dt 
				else dateadd(m, datediff(m, 0, @dt), 1)-1
				end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ralmadaCommented:
and btw,

>> @dt variable as nvarchar(25), <<

@dt should be declared as datetime, not nvarchar.
0
ASPDEVAuthor Commented:
ralmada,

Thanks for your solution. I changed the query to work, getdate() instead of @dt :

case when month(@dt) = month(getdate()) and year(@dt) = year(getdate()) then getdate()
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.