Query with date question

I need to return all records in which the date 'lastmodified' +
'responsible' which is a number (of days) is the same as today's date.
I have the following condition in my sql but even though it should return
some records it doesnt:
WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
getdate())
Activities.responsible = Its the number of days being added
Activities.lasmodified = Its a date say (01/01/2005)
If the SUM of both is todays date the recordset should be returned, I don't
know if this has to do with the fact that seconds and minutes might be
involved ?
Any help is appreciated.
AleksThis should do it:
WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
AND GETDATE()
... Unfortunately, I believe this will force a table or index scan; I'm not
sure how to get around it given your current schema. If you can, instead of
keeping the number of days, keep the "end" date. Then you'll be able to
write a query that's capable of using an index.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Much more efficient query (assuming an index on LastModified):
DECLARE @.d SMALLDATETIME
SET @.d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= (@.d - a.responsible)
AND a.LastModified < (@.d +1 - a.responsible)
You always want the column with the index on its own on one side of the
equation. This will allow for an index s instead of a scan. And yes,
this is because LastModified has minutes and seconds, presumably, and
GETDATE() certainly does. The DATEADD/DATEDIFF trick I did up there
converted it to a time of midnight, which makes it easy to find date values
anywhere >= that day and < that day + 1. The query also takes advantage of
implicit integer math with datetime/smalldatetime values, but the purists
might want this instead:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
http://www.aspfaq.com/
(Reverse address to reply.)
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I
don't
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>|||Did not work
The sum of lastworked and responsible should be today's date. I tried that
sql and returned nothing.
Aleks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||This seems to work though.
(Activities.LastModified+responsible) between (GETDATE()-1) and
(getdate())
Would that be alright ?
A
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OULqxl2IFHA.588@.TK2MSFTNGP15.phx.gbl...
> This should do it:
> WHERE Activities.LastModified BETWEEN (GETDATE() - Activities.Responsible)
> AND GETDATE()
>
> ... Unfortunately, I believe this will force a table or index scan; I'm
> not
> sure how to get around it given your current schema. If you can, instead
> of
> keeping the number of days, keep the "end" date. Then you'll be able to
> write a query that's capable of using an index.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:eWfhxe2IFHA.1476@.TK2MSFTNGP09.phx.gbl...
> don't
>|||"Aleks" <arkark2004@.hotmail.com> wrote in message
news:egjIvv2IFHA.3568@.TK2MSFTNGP10.phx.gbl...
> This seems to work though.
> (Activities.LastModified+responsible) between (GETDATE()-1) and
> (getdate())
> Would that be alright ?
Personally -- if you can't change the schema -- I would go with Aaron's
second query:
SELECT ...
FROM Activities a
..
WHERE a.LastModified >= DATEADD(DAY, 0 - a.responsible, @.d)
AND a.LastModified < DATEADD(DAY, 1 - a.responsible, @.d)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Alek,
To explain your problem, You must understand theat getDate() function
returns date and Time, so your query will only return records where the
expression
DATEADD(day,Activities.responsible , Activities.Lastmodified )
is EXACTLY equal to the current date AND time Value, It is very unlikely
that any records will satisfy that...
The only way to do this is to write your predicate as a between, (or as a >=
AND < ), where all records are returned where the value in the dataabase
table is between 2 calculated BOUNDARY values, based on the current date and
time...
so you would either have
Activities.Lastmodified Between @.LowDate And @.HighDate, or
Activities.Lastmodified > @.LowDate And Activities.Lastmodified <= @.HighDate
(NOTE: BETWEEN implies >= AND <= )
Two questions need to be answered, to determine what those
1) When you say "= getdate()" What, exactly do you mean? - do you want,
a) ALl the records that occurred on that specific Calendar DAY?, or
b) All the records that occurred within exactly 12/(24?) hours of a
specific Date and Time?
If it's the former (which I suspect is the case), than your Boundary dates
will be Midnight, in the am, on a specific calculated date :
DateAdd(day, - Responsible, getdate()), converted to strip off the time...
Convert(VarChar(8), DateAdd(day, - Responsible, getdate()), 112)
and the high date would be one day later, again at midnigjt...
Convert(VarChar(8), DateAdd(day, 1 - Responsible, getdate()), 112)
or.
Where Activities.Lastmodified >= Convert(VarChar(8), DateAdd(day, -
Responsible, getdate()), 112)
AND Activities.Lastmodified < Convert(VarChar(8), DateAdd(day, 1 -
Responsible, getdate()), 112)
You have to split off the time portion if you only want those records from a
specific calendar day...
cannot "Aleks" wrote:

> I need to return all records in which the date 'lastmodified' +
> 'responsible' which is a number (of days) is the same as today's date.
> I have the following condition in my sql but even though it should return
> some records it doesnt:
>
> WHERE (DATEADD(day ,Activities.responsible , Activities.Lastmodified ) =
> getdate())
>
> Activities.responsible = Its the number of days being added
> Activities.lasmodified = Its a date say (01/01/2005)
> If the SUM of both is todays date the recordset should be returned, I don'
t
> know if this has to do with the fact that seconds and minutes might be
> involved ?
> Any help is appreciated.
> Aleks
>
>