March 19, 2008 at 1:09 am
Hello everyone, I m using SQL SERVER 2005, here is my query that retuns the FormsCount of last 7 days from the current date, but this
query returns nothing, i think some time issues , in my table LastUpdate column datatype id datetime and it contains date with time
, i m trying to get date without time by converting it into varchar, but stills returns nothing Kindly chk it whats
wrong in this query?
select distinct
(select count(*) from TvsRecords where LastUpdate= convert(varchar(10),getdate(),101)) as '7',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -1, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '6',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -2, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '5',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -3, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '4',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -4, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '3',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -5, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '2',
(select count(*) from TvsRecords where LastUpdate = DATEADD(DAY, -6, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101)))) as '1'
from TvsRecords
plz reply me asap,
Thanx in Advance.
March 19, 2008 at 3:00 am
on the right hand side of your comparisons in the where clause (DATEADD(DAY, -1, DATEDIFF(DAY, 0, convert(varchar(10),getdate(),101))))) you get a date and the time part of it will be 0:0:0.0
On the left hand you are comparing it with a datetime value that still has the time information. The two will be only equal, if your LastUpdate column stores datetimes with the time information set to 0:0:0.0
Regards,
Andras
March 19, 2008 at 3:16 am
Andras is right
to fix, you could either compare the LastUpdate using between or convert it to 'only date'
select count(*) from TvsRecords where LastUpdate between
convert(datetime, convert(varchar(10),getdate()-1,101), 101)
and convert(datetime,convert(varchar(10),getdate(),101), 101) as '1'
probably there's a more eficient way to do this 😉
LE: edited as i did not revert to datetime. think it s better than comparing to string 🙂
March 19, 2008 at 8:51 am
Not having the DDL for your table or any sample data, I came up with this:
select
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) and LastUpdate < getdate()
then 1
else 0
end) as '7',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 1 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0)
then 1
else 0
end) as '6',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 2 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0) - 1
then 1
else 0
end) as '5',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 3 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0) - 2
then 1
else 0
end) as '4',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 4 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0) - 3
then 1
else 0
end) as '3',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 5 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0) - 4
then 1
else 0
end) as '2',
sum(case when LastUpdate >= dateadd(dd, datediff(dd, 0, getdate()), 0) - 6 and LastUpdate < dateadd(dd, datediff(dd, 0, getdate()), 0) - 5
then 1
else 0
end) as '1'
from
dbo.TvsRecords
group by
LastUpdate
Give it a try and see if it gives you what you are looking for.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply