ASPDEV
asked on
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.
NOTE: Here @dt is the temp variable, to pass value.
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)NOTE: Here @dt is the temp variable, to pass value.
ASKER
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).
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).
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and btw,
>> @dt variable as nvarchar(25), <<
@dt should be declared as datetime, not nvarchar.
>> @dt variable as nvarchar(25), <<
@dt should be declared as datetime, not nvarchar.
ASKER
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()
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()
please check what @dt variable type and @dt variable date format it is returning