Compare Date Values!

Hi all,
In the following query i want to compare only the date value portions of the
'attempt_dt' columns in the where clause.
All time related information information is to be ignored!!!!
SELECT
a.attempt_dt,
(SELECT
Count(asmt_v2_employee_id)
FROM
asmt_v2_attempts a2
INNER JOIN
asmt_v2_question_results r
ON
a2.asmt_v2_attempt_id = r.asmt_v2_attempt_id
WHERE
r.asmt_v2_question_id = 170
AND
a2.attempt_dt = a.attempt_dt //compare date portions of values
)
FROM
asmt_v2_attempts a
What is the best way to do this? Using some sort of date functionality?
Examples & help appreciated!!!
Cheers,
AdamAdam
> In the following query i want to compare only the date value portions of
> the 'attempt_dt' columns in the where clause.
SELECT CAST(CONVERT(VARCHAR(10),GETDATE(),112)A
S DATETIME)
Note: By using above tecnique you may face a perfomance problem , the
optimizer will not be able to use an index on this column
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:ugUp21vyFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> In the following query i want to compare only the date value portions of
> the 'attempt_dt' columns in the where clause.
> All time related information information is to be ignored!!!!
> SELECT
> a.attempt_dt,
> (SELECT
> Count(asmt_v2_employee_id)
> FROM
> asmt_v2_attempts a2
> INNER JOIN
> asmt_v2_question_results r
> ON
> a2.asmt_v2_attempt_id = r.asmt_v2_attempt_id
> WHERE
> r.asmt_v2_question_id = 170
> AND
> a2.attempt_dt = a.attempt_dt //compare date portions of values
> )
> FROM
> asmt_v2_attempts a
> What is the best way to do this? Using some sort of date functionality?
> Examples & help appreciated!!!
> Cheers,
> Adam
>|||Adam
try this .
But remeber it works only when u r using(=) NOT >< etc. It still uses
indexes.
DAY(a2.attempt_dt) = DAY(a.attempt_dt ) AND MONTH(a2.attempt_dt) =
MONTH(a.attempt_dt ) AND YEAR(a2.attempt_dt) AND YEAR(a.attempt_dt )
Regards
R.D
--Knowledge gets doubled when shared
"Uri Dimant" wrote:

> Adam
> SELECT CAST(CONVERT(VARCHAR(10),GETDATE(),112)A
S DATETIME)
> Note: By using above tecnique you may face a perfomance problem , the
> optimizer will not be able to use an index on this column
>
> "Adam Knight" <adam@.pertrain.com.au> wrote in message
> news:ugUp21vyFHA.2556@.TK2MSFTNGP10.phx.gbl...
>
>|||OOPS
try this
(DAY(a2.attempt_dt) = DAY(a.attempt_dt )) AND (MONTH(a2.attempt_dt) =
MONTH(a.attempt_dt ) ) AND (YEAR(a2.attempt_dt) = YEAR(a.attempt_dt ) )
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Adam
> try this .
> But remeber it works only when u r using(=) NOT >< etc. It still uses
> indexes.
> DAY(a2.attempt_dt) = DAY(a.attempt_dt ) AND MONTH(a2.attempt_dt) =
> MONTH(a.attempt_dt ) AND YEAR(a2.attempt_dt) AND YEAR(a.attempt_dt )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Uri Dimant" wrote:
>|||SQL Server cannot do an index s if the column name is inside a function c
all. How could it? The
value need to be calculated before the value is known...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:FFAD2A23-83BC-4F0D-81FB-EB92D7CD4B1F@.microsoft.com...
> Adam
> try this .
> But remeber it works only when u r using(=) NOT >< etc. It still uses
> indexes.
> DAY(a2.attempt_dt) = DAY(a.attempt_dt ) AND MONTH(a2.attempt_dt) =
> MONTH(a.attempt_dt ) AND YEAR(a2.attempt_dt) AND YEAR(a.attempt_dt )
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Uri Dimant" wrote:
>|||Thanks Tibor, My intention was
try this .
But remeber it works only when u r using(=) NOT >< etc. It still DONT use
indexes.
DAY(a2.attempt_dt) = DAY(a.attempt_dt ) AND MONTH(a2.attempt_dt) =
MONTH(a.attempt_dt ) AND YEAR(a2.attempt_dt) AND YEAR(a.attempt_dt )
Regards
R.D
--Knowledge gets doubled when shared
"Tibor Karaszi" wrote:

> SQL Server cannot do an index s if the column name is inside a function
call. How could it? The
> value need to be calculated before the value is known...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:FFAD2A23-83BC-4F0D-81FB-EB92D7CD4B1F@.microsoft.com...
>
>|||R.D.,
I'm sorry, but I don't understand what you are saying here. Indexes can be u
sed for equal, greater
than, less than, ranges etc. If possible in the first place, of course (i.e.
, if you don't have the
column inside a function all or don't do calculations on the column side).
Perhaps I just misread you? Can you post some examples of WHERE clauses and
say in which case an
index can be used (s) and where it cannot be used?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:9767E0FD-015E-402F-9D6B-0B8B7BA3847A@.microsoft.com...
> Thanks Tibor, My intention was
> try this .
> But remeber it works only when u r using(=) NOT >< etc. It still DONT use
> indexes.
> DAY(a2.attempt_dt) = DAY(a.attempt_dt ) AND MONTH(a2.attempt_dt) =
> MONTH(a.attempt_dt ) AND YEAR(a2.attempt_dt) AND YEAR(a.attempt_dt )
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Tibor Karaszi" wrote:
>|||Tibor.
Thank you for your post. Sorry for the confusion.
> Perhaps I just misread you? Can you post some examples of WHERE clauses an
d say in which case an
> index can be used (s) and where it cannot be used?
I was just telling that I missed 'DOES'NT' and Paranthesis in my first
post.In my first post I was referring to Uris where in he said that his
approach does not use indexes as columns are in functions. So I was also
explaining him that even my approach does not use indexes.
These are two sentences
First one says you can't compare dates with day/month/year function using
</> because they return integers. I mean 01/01/2005 >02/02/2004
but month(firstdate) < month(seconddate). but when we use = there is no
problem comparing because we are comaring obsolute int of two dates.
I was just telling that It was a typographi error to miss DOES NOT in my
first post.
Regards
R.D
--Knowledge gets doubled when shared
"Tibor Karaszi" wrote:
> R.D.,
> I'm sorry, but I don't understand what you are saying here. Indexes can be
used for equal, greater
> than, less than, ranges etc. If possible in the first place, of course (i.
e., if you don't have the
> column inside a function all or don't do calculations on the column side).
> Perhaps I just misread you? Can you post some examples of WHERE clauses an
d say in which case an
> index can be used (s) and where it cannot be used?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:9767E0FD-015E-402F-9D6B-0B8B7BA3847A@.microsoft.com...
>
>