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?
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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
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.